Add a prefix/postfix to all the column names in a table when using "firstobs" for creating lag.

[Saurabh_Rana]

I am using firstobs=1,2,3...etc for creating lag in the table,but what I want is that when I create a new table with lag=1 for example then all the columns name in the table should have a postfix "_1".

Like that I want to do "_2" for table with lag=2(firstobs=2).

For example =>

Column names of table "T"

Customer_Name  Customer_ID

Column names of table "T1" with lag=1(firstobs=1)

 Customer_Name_1  Customer_ID_1

[s_lassen · Accepted solution]

This can be done with a simple macro, once you have the names of the variables:

%macro setlag(ds,vars,lag);
%local i w;
  &ds(keep=&vars rename=(
    %do i=1 %to %sysfunc(countw(&vars));
       %let w=%scan(&vars,&i);
       %do; &w=&w._&lag%end;  /* the %do...%end here is just to avoid spurious blanks */
       %end;
     ) firstobs=&lag)
%mend;

The macro generates the dataset name with the options you want.

To get the variable names, you can use SQL:

proc sql noprint;
  select name into :vars separated by ' ' 
  from dictionary.columns where libname='WORK' and memname='HAVE';
quit;

(change the condition to match your input table)

You can then use the macro like this:

data want;
  set have in=in0;
  set %setlag(have,&vars,1) in=in1;
  set %setlag(have,&vars,2) in=in2;
run;

I put the keep= statement in the macro in case you only want some of the variables, e.g.:

set %setlag(sashelp.class,name age,3);

[Saurabh_Rana]

Since I am new to SAS I don't have much knowledge on how to use macros in SAS, so is there any alternative approach where we can do the same without using macros.

[Kurt_Bremser]

---

@Saurabh_Rana wrote:

Since I am new to SAS I don't have much knowledge on how to use macros in SAS, so is there any alternative approach where we can do the same without using macros.

---

Then you have to write the multiple lines for the "look-ahead" (because that's what you actually do) in the MERGE statement yourself.

merge
  have
  have (firstobs=2 rename=(var1=_1_var1))
  /* and so on */
;

[Saurabh_Rana]

OK , thanks for answering

[Kurt_Bremser]

Since you need to repeat code, a macro is the tool of choice:

%macro create_lags(inlib=,outlib=WORK,inds=,outds=,num=1);

%local i j;

proc sql noprint;
select name into :names separated by " " from dictionary.columns
where libname = upcase("&inlib.") and memname = upcase("&inds.");
quit;

data &outlib..&outds.;
merge
  &inlib..&inds.
%do i = 1 %to &num.;
  &inlib..&inds. (firstobs=%eval(&i.+1) rename=(
  %do j = 1 %to %sysfunc(countw(&names));
    %scan(&names.,&j.)=_&i._%scan(&names.,&j.)
  %end;
  ))
%end;
;
run;

%mend;

%create_lags(inlib=sashelp,inds=class,outds=class,num=2)