automate the variable naming through sas in a macro

[andy_wk]

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.

[RW9]

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.

[Astounding]

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;

  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.

[Reeza]

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?

[jwillis]

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 " ";

[andy_wk]

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.