topic Resolving a macro variable to a column name in SAS Programming

Resolving a macro variable to a column name

ebowen — Tue, 11 Feb 2014 21:17:23 GMT

I'm have a dataset with sequentially numbered variable (column) names that contain data for different years. I want to compile all of these data into two different variables -- call them PreviousYear and CurrentYear -- based on which year it currently is in the dataset. In the data snippet below, for example, the column ENR_F03 would be the current year's data for the year 2003, but the previous year's data for the year 2004. So for the first observation, I would like PreviousYear to take the value from column ENR_F02, and CurrentYear to take the value from column ENR_F03. But in the second observation, I would like PreviousYear to take a value from column ENR_F03, and CurrentYear to take the value from ENR_F04, etc.

ENR_F02	ENR_F03	ENR_F04	Year
Y	Y	N	2003
N	Y	N	2004

I have started with the following code, which may not be the best approach here, but it raises a question for me. Is it possible to resolve a macro variable as a column name? So far, I have been able to put the column names I want into the variables, but when I try to get the values of those columns back out, all I get is the value of the macro variable itself. So PreviousYear and CurrentYear end up being ENR_F02 and ENR_F03, etc. I could probably code this as a series of if statements that pull data from different columns, but I thought I would see if there's a better way. Thanks for your help!

data test;

set data;

CY=PUT(wage_yr,4.);

PY=PUT(wage_yr-1,4.);

call symput('ENPY','ENR_F'||substr(PY,3,2));

call symput('ENCY','ENR_F'||substr(CY,3,2));

PreviousYear = symget('ENPY');

CurrentYear= symget('ENCY');

run;

Re: Resolving a macro variable to a column name

Reeza — Tue, 11 Feb 2014 21:26:41 GMT

Perhaps post what your data looks like and what you want. Your method seems roundabout and I'm wondering if there isn't a way to avoid the macros, by using arrays for instance.

EDIT: Also look into the vvaluex function for retrieving the value from a variable in a datastep.

SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition

Re: Resolving a macro variable to a column name

ebowen — Tue, 11 Feb 2014 22:00:11 GMT

Unfortunately, I can't post the data, because it's confidential. But it looks pretty much like what I posted -- sequentially numbered columns referring to different years, only the real data has multiple years instead of just the three I put in the sample data. It does seem like there ought to be a way to deal with it in terms of arrays, but I'm not enough of an array aficionado to figure it out.

On your other point, the vvaluex function works well enough for my purposes. I have been able to get the data out of the columns by modifying the data step with that function as follows:

data test;

set data;

CY=PUT(wage_yr,4.);

PY=PUT(wage_yr-1,4.);

call symput('ENPY','ENR_F'||substr(PY,3,2));

call symput('ENCY','ENR_F'||substr(CY,3,2));

PreviousYear = vvaluex(symget('ENPY'));

CurrentYear= vvaluex(symget('ENCY'));

run;

I would welcome any further comments on this thread to see if there's a better way to handle this.

Re: Resolving a macro variable to a column name

Reeza — Tue, 11 Feb 2014 22:06:52 GMT

Make fake data that mimics your data structure with the output matching the desired output

Arrays are still probably a better solution to this.

EDIT: You're not using vvaluex properly.

data test;

set data;

CY=PUT(wage_yr,4.);

PY=PUT(wage_yr-1,4.);

PreviousYear = vvaluex('ENR_F'||substr(PY,3,2));

CurrentYear= vvaluex('ENR_F'||substr(CY,3,2));

run;

Re: Resolving a macro variable to a column name

Astounding — Tue, 11 Feb 2014 22:37:36 GMT

This looks more like an application for arrays, such as:

data want;

set have;

array fnr {2002:2004} ENR_F02 ENR_F03 ENR_F04;

if (2002 <= year <= 2004) then do;

current_year = fnr{year};

if year > 2002 then previous_year = fnr{year-1};

end;

run;

Is this in the ballpark for what you are looking to do?

Re: Resolving a macro variable to a column name

ebowen — Wed, 12 Feb 2014 16:03:49 GMT

With some modifications to fit my data, this works great. I figured there had to be a way to do this with arrays. Thanks for the help!