topic Re: automate the variable naming through sas in a macro in SAS Procedures

automate the variable naming through sas in a macro

andy_wk — Fri, 09 May 2014 13:55:26 GMT

I have the following code

%macro test; 
data ttest; set test; 
%do var14_t_0=lag(var14); 
%do i=1 %to 6; var14_t_&i=lag(var14_t_%eval(&i-1)); 
%end; 
drop var14; 
run; 
%mend; 
%test;

the var14 is a variable name with which, I'm using the lag function. However, I have a lot of variables in my table. I'm trying to automate the variable naming but I'm sure how to do this.

I thought about the following:

Use proc sql to create a table like below:

proc sql; create table label as select name from dictionary.columns where memname = upcase('Afr') and name not in ('date');

Once the table created, I would only need to take the variable name from inside the label table. But the issue is I'm not sure how to link the code above with my table.

I'm not saying I need the table and I may need something else but I'm unsure of the steps as I'm new to SAS.

Thanks in advance for any tips you can provide.

Re: automate the variable naming through sas in a macro

RW9 — Fri, 09 May 2014 14:12:50 GMT

Not sure what you are trying to get to here, perhaps provide test data and what you want out. There are various methods you could try, arrays for instance, or call executing to generate the code.

Re: automate the variable naming through sas in a macro

Astounding — Fri, 09 May 2014 14:21:04 GMT

Since you already created LABEL, let's take it from that point. One possible line of attack is CALL EXECUTE.

data _null_;

call execute ('data want; set have;');

do until (done);

set label end=done;

do _n_=1 to 6;

call execute(trim(name) || '_' || put(_n_, 1.) || '= lag' || put(_n_,1.) || '(' || name || ');');

end;

call execute('run;');

stop;

run;

The advantage: it's relatively short and direct. The disadvantage: the tools are somewhat sophisticated, not where you would want to venture when relatively new to SAS. But no approach will be simple since you are essentially writing a program whose task it is to construct the program that you need.

Good luck.

Re: automate the variable naming through sas in a macro

Reeza — Fri, 09 May 2014 14:25:22 GMT

What are you trying to do overall, why do you need this specific naming convention?

If calculating lagged variables can you use proc expand which will do it automatically?

Re: automate the variable naming through sas in a macro

jwillis — Fri, 09 May 2014 15:05:26 GMT

Andy,

I am certainly not an expert like Reeza and Astounding, however, I have a code style that has worked for me in the past in similar situations. When I want to build and populate variable names with numeric suffixes, I use a macro to build the array, name the variables in the array; and macro code to build the data step coding; This worked for me however I did include the "_t_" in the names. I also did not include any code to place default values in the members of the arrays'. With this style, using varnums, you can build as many new variables as you wish.

data values;
input date $7. var14 var41 8.;
datalines;
2013M8 -25.6 -30
2013M9 -24.5 -27.3
2013M10 -26.4 -25.7
2013M11 -32.6 -29.2
2013M12 -30.7 -27.3
2014M01 -29.2 -30.3
;
run;

          title " values ";
     proc print data=values (obs=7);
     run;
          title " ";

%macro bildit(varnums);
proc sql; drop table work.wantvals; quit;
        data wantvals;
          set values;
var140 = var14;
var410 = var41;
%do i=1 %to &varnums.;                  /* format the variable in the order you want them displayed/printed */
     format var14&i. 8.;
%end;
%do i=1 %to &varnums.;
     format var41&i. 8.;
%end;
           array lst14 {*}

                      %do i=1 %to &varnums.;
                         var14&i
                      %end;
            ;
           array lst41 {*}
                      %do i=1 %to &varnums.;
                         var41&i
                      %end;
            ;
          %do k=1 %to &varnums.;      /* order of the variables has been set so they can be loaded in any order */
            lst14{&k.} = lag&k.(var14);
            lst41{&k.} = lag&k.(var41);
          %end;
drop var14 var41;
run;
%mend bildit;
%bildit(6);

title " wantvals ";
proc print data=work.wantvals (obs=7);
run;
title " ";

Re: automate the variable naming through sas in a macro

andy_wk — Mon, 12 May 2014 13:54:49 GMT

Hi everyone,

SAS seems to be daunting when you are beginning with it.

I cannot carry on this part as my man-a asked to do it differently. Pffff

I spent a lot of time on that one.

Thank you for your input.

I'm keeping it, just in case.

Cheers.