How do I use a variable for a table name?

[Mudcat]

Hi,

I'm new to SAS. I've searched through the posts but without knowing
the defintions of things yet haven't been successful in finding the
answer.

I am trying to create a SAS script that creates table or datasets
using a variable name. I'm updating existing SAS scripts that are run
on a monthly basis where the names have to be updated in multiple
locations each month. As a result, I'd like to create a variable at
the top for each month

For example...

It currently looks like this:

data lib.monthly_sep_oct_transition;
set lib.month_sep lib.month_oct;
....
<do stuff>
...
run;

I'd like to update them to look like this:

%let cur_month = oct
%let prv_month = sep

data lib.monthly_&prv_month_&cur_month_transition;
set lib.month_&prv_month lib.month_cur_month
....

Using variables I've tried several variations using quotations and
dots but just can't seem to make it work. An example or link to an
existing thread explaining this would be great.

Thanks.

[Patrick]

You need to tell SAS is where your macro variable ends. That's done
with a dot.

eg:
%let cur_month = oct
%let prv_month = sep
lib.monthly_&prv_month._&cur_month._transition

Hope that helps
Patrick

P.S: Should you ever need a dot after a macro variable resolved then
you have to use two consecutive dots.
eg:
%let saslib=lib;
%let cur_month = oct
%let prv_month = sep
&saslib..monthly_&prv_month._&cur_month._transition

[Nordlund, Dan (DSHS/RDA)]

-----Original Message-----
Behalf Of Mudcat
Sent: Thursday, December 27, 2007 4:06 PM
Subject: How do I use a variable for a table name?
Hi,
I'm new to SAS. I've searched through the posts but without knowing
the defintions of things yet haven't been successful in finding the
answer.
I am trying to create a SAS script that creates table or datasets
using a variable name. I'm updating existing SAS scripts that are run
on a monthly basis where the names have to be updated in multiple
locations each month. As a result, I'd like to create a variable at
the top for each month
For example...
data lib.monthly_sep_oct_transition;
set lib.month_sep lib.month_oct;
....
<do stuff>
...
run;
%let cur_month = oct
%let prv_month = sep
data lib.monthly_&prv_month_&cur_month_transition;
set lib.month_&prv_month lib.month_cur_month
....
Using variables I've tried several variations using quotations and
dots but just can't seem to make it work. An example or link to an
existing thread explaining this would be great.
Thanks.

Careful typing and a judicious use of periods will get you where you want to go. It is not always necessary to use a period (.) to terminate a macro variable, but until you get used to where the exceptions are you might want to always use a period at the end of macro names (it never hurts to use them). I used them at the end of the macro variables on the second line where they are not necessary, but they are necessary on the first line. You had also left out a & on the second line of your example above.

data lib.monthly_&prv_month._&cur_month._transition;
set lib.month_&prv_month. lib.month_&cur_month. ;
....

Hope this is helpful,

Dan

Daniel J. Nordlund
Research and Data Analysis
Washington State Department of Social and Health Services
Olympia, WA 98504-5204

[Patrick]

Dan
This approach with having the dot as part of the macro var value for
libnames (and then set the macro var to missing during development) is
a very nice idea. Thanks for that!

Mudcat
Reading your post again I thought the code below might be of some
interest for you. Based on the current date (the date the job runs) it
generates the name for the current month and the previous month. Using
something like this would allow you to run the code on a monthly basis
without any code changes.
Patrick


data _null_;
CurMonthName=put(today(),monname3.);
call symput('cur_month',CurMonthName);

PrvMonthDate=INTNX('month', today(), -1,'B');
PrvMonthName=put(PrvMonthDate,monname3.);
call symput('prv_month',PrvMonthName);
run;

%put cur_month= &cur_month;
%put prv_month= &prv_month;

[Nordlund, Dan (DSHS/RDA)]

-----Original Message-----
Behalf Of Patrick
Sent: Thursday, December 27, 2007 4:25 PM
Subject: Re: How do I use a variable for a table name?
You need to tell SAS is where your macro variable ends. That's done
with a dot.
%let cur_month = oct
%let prv_month = sep
lib.monthly_&prv_month._&cur_month._transition
Hope that helps
Patrick
P.S: Should you ever need a dot after a macro variable resolved then
you have to use two consecutive dots.
%let saslib=lib;
%let cur_month = oct
%let prv_month = sep
&saslib..monthly_&prv_month._&cur_month._transition

With regard to Patrick's P.S., I usually run SAS code in batch mode, even while developing. I sometimes use a macro to specify a libname for temporary files that I want kept even if my batch file crashes. And rather than use a .. as in the above example, I include one of the periods as part of the macro variable value. So, something like this:

libname templib "some_path";
%let temp=templib. ;

data &temp.temporary_file;
...
run;

This allows me at a later point in time to set the macro value to null, and the file name will still resolve to a valid SAS dataset name (which won't work if one uses a .. ), but as a single level file reference it becomes a temporary work dataset.

Hope this is helpful,

Dan


Daniel J. Nordlund
Research and Data Analysis
Washington State Department of Social and Health Services
Olympia, WA 98504-5204

[Jan Selchau-Hansen]

Post by Mudcat
Hi,
I'm new to SAS. I've searched through the posts but without knowing
the defintions of things yet haven't been successful in finding the
answer.
I am trying to create a SAS script that creates table or datasets
using a variable name. I'm updating existing SAS scripts that are run
on a monthly basis where the names have to be updated in multiple
locations each month. As a result, I'd like to create a variable at
the top for each month
For example...
data lib.monthly_sep_oct_transition;
set lib.month_sep lib.month_oct;
....
<do stuff>
...
run;
%let cur_month = oct
%let prv_month = sep
data lib.monthly_&prv_month_&cur_month_transition;
set lib.month_&prv_month lib.month_cur_month
....
Using variables I've tried several variations using quotations and
dots but just can't seem to make it work. An example or link to an
existing thread explaining this would be great.
Thanks.

Mudcat, I think You are on the wrong path.

It is NOT a good idea to store Your data in different tables for every
month.
Store them all in ONE table, with a variable to indicate the date/month
whatever.
(please use a standard SAS-date)
Then use the excelent SAS-functions to extract what You need from this table
when You need it.
Look into the functions INTNX, INTCK

Jan Selchau-Hansen

[Mudcat]

Thanks for the quick and helpful responses. That's exactly what I
needed.

As for storing data tables for every month, at this point I'm
inheriting structures already built, so I don't have much choice but
to use them. Initially I just want to make the scripts need less
manual intervention. However at some point in the future, once I know
a little more about what I'm doing, I will look into optimizing the
scripts and possibly re-writing them. I will definitely take that into
account. I believe they split the tables into months initially because
of the large amount of data they were working with, and they wanted
smaller chunks of data.

Thanks.