BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Saurabh_Rana
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
s_lassen
Meteorite | Level 14

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

 

View solution in original post

5 REPLIES 5
s_lassen
Meteorite | Level 14

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
Obsidian | Level 7

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
Super User

@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 */
;

 

 

Kurt_Bremser
Super User

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)

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 4034 views
  • 0 likes
  • 3 in conversation