DATA Step, Macro, Functions and more

Resolving a macro variable to a column name

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

Resolving a macro variable to a column name

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_F02ENR_F03ENR_F04Year
YYN2003
NYN2004

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;


Accepted Solutions
Solution
‎02-11-2014 05:37 PM
Super User
Posts: 5,516

Re: Resolving a macro variable to a column name

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?

View solution in original post


All Replies
Super User
Posts: 19,860

Re: Resolving a macro variable to a column name

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

Contributor
Posts: 28

Re: Resolving a macro variable to a column name

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.

Super User
Posts: 19,860

Re: Resolving a macro variable to a column name

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

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;

Solution
‎02-11-2014 05:37 PM
Super User
Posts: 5,516

Re: Resolving a macro variable to a column name

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?

Contributor
Posts: 28

Re: Resolving a macro variable to a column name

Posted in reply to Astounding

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!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 2177 views
  • 3 likes
  • 3 in conversation