SAS macro to generate a long list of variables

[smilingmelbourne]

Hi, I've been downloading quarterly panel data from Datastream. The data looks something like below

FirmID _1_Jan_95 _1_Feb_95....................... _1_Dec_10... _1_Apr_11
A 8.4 52.4 252 54.2
B 45.6 52.4 234 85.4
...

I wrote a macro to import all Excel sheets into SAS, each sheet being a separate SAS dataset. Then, the macro clean invalid values, converts char-to-num values for these values (it's numeric but after importing SAS makes it char instead of num), append/stack up all these datasets on top of each other, and finally transpose to panel data structure that looks like below

FirmID Date VariableName
A _1_Jan_95 8.4
A _1_Feb_95 52.4
...


I have to write a long list of dates to loop over. For example,

%let columnname= _1_Jan_95 _1_Feb_95............................. - 1_Apr_2011;

Based on this, I looped over this using %SCAN(&columname, &i) in cleaning, converting data.

I found out that sometimes I mistyped the columnname. Say, instead of _1_Jul_95 I mistyped it to be 1_Jul_1995 or _1_July_95. It's annoying to make this typing errors.

So I created a macro to generate a variable list like below


/************************************************************************/
/* A macro to generate a long list of variable names typically found in */
/* a Worldscope/Datastream data set after being imported into SAS */
/* Example: After importing a DS/WC Excel sheet into SAS, Column 1 looks*/
/* like below: */
/* _1_Mar_2008 */
/* gen_vars accepts 5 arguments */
/* - day=: the day displayed like 1 above */
/* - mon=: a list of months of data for a given year, say, Mar, Jun, Dec*/
/* - mon_num: the number of months above, say 3 for months above*/
/* - year= a list of years data collected over, say 1995 1996, etc... */
/* - year_num=: the number of years above */
/************************************************************************/

%macro gen_vars (day=, mon=, mon_num=, year=, year_num=);
%local i j;
%do i=1 %to &year_num %by 1; /*Loop over years*/
%do j=1 %to &mon_num %by 1; /*Loop over months*/
%put _&day._%scan(&mon, &j)_%scan(&year, &i);
%end;
%end;
%mend gen_vars;


MY TROUBLE is, I don't know how to assign the RETURN VALUES from this gen_vars macro. In Matlab/R, when I created a function with a return value and if I want to assign the value returned by the function, I simply do sth like: myvar = myfunction(argument), and that's it, the return value of myfunction(argument) is assigned to myvar.

But in SAS, how can I do this? Can I do the following?

%let mon=Jan Mar Jun Sep Dec;
%let year=95 96 97 98 99 00 01 02 03 04 05 06 07 08 09 10 11;

%let varlist = %gen_vars(day=1, mon=&mon, mon_num=4, year=&year, year_num=17);

I've tried this but it gives errors, but I can't figure out how it is wrong and where.

Can anyone please help?

Thanks so much for your time.

[Ksharp]

You can use dictioary.columns to get the varname list for the first table.
And code some the to filter the varname with dictionary.columns


Ksharp

[smilingmelbourne]

Hi Ksharp, thanks a lot for the tip. Now I know how to generate a list of variables using dictionary.columns. Thanks Message was edited by: smilingmelbourne

[Patrick]

Hi

Not really sure why you need this long variable list in first place.

May be below example gives you an idea how to tackle the challenge with less macro and more SAS coding.
Especially the use of column ':' as wildcard for referencing SAS variables might be useful for you.

data have;
infile datalines dlm=' ';
attrib _: format=8.;
input FirmID $ _1_Jan_95 _1_Feb_95 _1_Mar_95 _1_Apr_95;
datalines;
A 8.4 52.4 252 54.2
B 45.6 52.4 234 85.4
;
run;

proc transpose data=have out=inter name=DateInt prefix=Value;
by FirmID;
var _:;
run;

data want(drop=DateInt);
set inter;
format date date9.;
Date=input(compress(DateInt,'_ '),?? date.);
run;

proc print data=want;
run;


/* use column wildcard to define array elements */
data loop;
set have;
array DateVars {*} _: ;

do i=1 to dim(DateVars);
put DateVars(i)=;
end;
run;

HTH
Patrick

[smilingmelbourne]

Hi Pattrick, thanks a lot for the help. I didn't know I could use the wildcard as you said. Very useful. Also, regarding the array I actually use arrays for this sort of problems, but in the present datasets I cannot make sure that every column has the same attributes (all numeric or all char). For example, January data might be characters because the first row of data is an error from data vendor (signs like $$$, ERRORs, etc...), so SAS takes January column as a char value and everything below is chars. February data has a valid data on the first row and so everything down the column is interpreted as a num. As a result, there's no pattern at all to ascertain which column is chars and which is nums due to data errors on the first row, although all columns should be num.

Your tips on wildcards is so useful. Thanks a lot

[Peter_C]

> Hi Pattrick, thanks a lot for the help. I didn't know
> I could use the wildcard as you said. Very useful.
> Also, regarding the array I actually use arrays for
> this sort of problems, but in the present datasets I
> cannot make sure that every column has the same
> attributes (all numeric or all char). For example,
> January data might be characters because the first
> row of data is an error from data vendor (signs like
> $$$, ERRORs, etc...), so SAS takes January column as
> a char value and everything below is chars. February
> data has a valid data on the first row and so
> everything down the column is interpreted as a num.
> As a result, there's no pattern at all to ascertain
> which column is chars and which is nums due to data
> errors on the first row, although all columns should
> be num.
>
You are solving symptoms of the problem rather identifying why your columns might have varying data types.
When reading text and CSV type data, a data step allows you to fix data types and column names that you "read" with INFILE and INPUT statements. If this is unfamiliar, you may find researching examples of those topics to be very rewarding in understanding how SAS manages and controls data it uses.
Where your data are supplied in excel workbooks there are options like DBTYPE which allow you to define how SAS should convert and provide the data to you in SAS.
I comment here only on those problems around loading data into your model.

peterC

[Patrick]

Based on what Peter posted:
May be you should open another thread telling us how this excel data looks like, how you import it into SAS and what challenges you're facing.

[ballardw]

Try this:
%macro gen_vars (day=, mon=, mon_num=, year=, year_num=);
%local i j result;
%do i=1 %to &year_num %by 1; /*Loop over years*/
%do j=1 %to &mon_num %by 1; /*Loop over months*/
/* here's the part you were missing to get a single output space delimited list*/
%let result=&result _&day._%scan(&mon, &j)_%scan(&year, &i);
%end;
%end;
&result
%mend gen_vars;

%let mon=Jan Mar Jun Sep Dec;
%let year=95 96 97 98 99 00 01 02 03 04 05 06 07 08 09 10 11;

%let varlist = %gen_vars(day=1, mon=&mon, mon_num=4, year=&year, year_num=17);

%put &varlist;

Your sytem settings for maximum macro value size may have an effect if this generates a long enough list.

If you nee

[smilingmelbourne]

Thanks, ballardw. That's so useful to me, because I've seen nothing like this in several SAS books I've read! So I must have a variable (in this case, result) to store the returned value. Message was edited by: smilingmelbourne