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
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);
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);
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.
@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 */
;
OK , thanks for answering
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)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.