topic Re: Create variable names in a loop in SAS Programming

Create variable names in a loop

chrwag — Fri, 04 Jan 2013 10:13:48 GMT

Hallo,

I have a data set with observations for several years (2010,2011,2012). So there is a variable year and some variables with premiums and so on. Now I try to process the data set and create particular variables for each year. e.g.

if uwyear = 2012 then do;

premium12 = premium;

......

ELSE DO;

premium12 = 0;

END;

This leads to a lot of coding which I try to avoid. My idea was to process the assignment statements in a loop. But I am stuck on how to create the variable names depending on the index variable.

DO i = 2010 to 2012;

premium&i = premium;

END;

I tried a lot of combinations with rename and call symput.

Is there a way to this? Hope someone can help me.

Christoph

Re: Create variable names in a loop

chrisj75 — Fri, 04 Jan 2013 11:37:24 GMT

One solution is to use an array...

/* Simplest form, hard-coded years */
data new ;

set mydata ;

array py{2010:2012} premium2010-premium2012 ;

yr = year(uwyear) ;

py{yr} = premium ;

run ;

/* Dynamic, based on years within data, and initialize to zero */

proc sql ;
select min(year(uwyear)) into :Y1 from mydata ;

selex max(year(uwyear)) into :Y2 from mydata ;

quit ;

data new ;

set mydata ;

array py{&Y1:&Y2} premium&Y1-premium&Y2 ;

/* Set to zero */

do i = lbound(py) to hbound(py) ;

py{i} = 0 ;

end ;

py{year(uwyear)} = premium ;

run ;

Re: Create variable names in a loop

chrwag — Fri, 04 Jan 2013 15:22:36 GMT

Hi Chris,

Your reply was very helpful. Thanks. To avoid writing several array statements (in my case up to 13) I used a multidimensional array.

%let min = 11;

%let max = 13;

array arr{2,20&min:20&max} net_qu&min-net_qu&max sale_nb&min-sale;

arr{1,uwyear} = net_qu;

arr{2,uwyear} = sale_nb;

So next year I just have to change my min and max values. 🙂

But there are still some variables that seems not that easy to handle. Here the year is in the middle of the variable. e.g. sale_11_rn . So I can't use a numbered range list. The people worked on this report before loved it to hard code everything. Maybe there is also a nice trick to avoid hard coding all the variables without change the names?

Christoph

Re: Create variable names in a loop

chrisj75 — Fri, 04 Jan 2013 15:38:41 GMT

Ok... similar to before, use a PROC SQL to get the distinct years in the data, but instead create a list of variable names...

e.g.

proc sql ;

select distinct(cats('sale_',year(uwyear),'_rn')) into :VAR1 separated by ' ' ;

quit ;

%PUT &VAR1 ;

VAR1 resolves to

sale_10_rn sale_11_rn sale_12_rn

then use that to define the variables for an array, i.e. (assuming &MIN = 10, &MAX = 12)

array sale{&MIN,&MAX} &VAR1 ;

Hope that helps.

Re: Create variable names in a loop

ballardw — Fri, 04 Jan 2013 15:46:56 GMT

It may be a pain but it could pay lots of dividends to change naming systems like sale_11_rn sale_12_rn to sale_rn_11 sale_rn_12. Every time I've gotten involved with a middle of variable scheme with chaning numbers it has lead to lots of headaches.

I'm also paranoid having had to deal with Y2K coding and would recommend using 4 digit year naming so there is no confusion in the inevitable stem_11_12 type named variable.

Re: Create variable names in a loop

chrwag — Fri, 18 Jan 2013 08:15:59 GMT

Sorry for my late response.

@chris:

Thanks. The Proc sql solution helped a lot.

@ballardw:

Of Course to change the variable names would be the best solution. But due to a lot of dependencies changing names is not that easy in this case.

Have a good weekend.

Christoph