topic Re: Add a prefix/postfix to all the column names in a table when using "firstobs" for crea in SAS Programming

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

Saurabh_Rana — Sun, 29 Mar 2020 08:40:29 GMT

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

Re: Add a prefix/postfix to all the column names in a table when using "firstobs" for crea

s_lassen — Sun, 29 Mar 2020 13:28:56 GMT

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

Re: Add a prefix/postfix to all the column names in a table when using "firstobs" for crea

Kurt_Bremser — Sun, 29 Mar 2020 14:22:32 GMT

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)

Re: Add a prefix/postfix to all the column names in a table when using "firstobs" for crea

Saurabh_Rana — Sun, 29 Mar 2020 14:52:56 GMT

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.

Re: Add a prefix/postfix to all the column names in a table when using "firstobs" for crea

Kurt_Bremser — Sun, 29 Mar 2020 15:02:02 GMT

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

Re: Add a prefix/postfix to all the column names in a table when using "firstobs" for crea

Saurabh_Rana — Sun, 29 Mar 2020 15:07:55 GMT

OK , thanks for answering