SAS - Creating combinations of different independant variables with lags

Question

I'd like to create dynamic entries in a data set (in SAS) formed using the names of variables (e.g. VarA, VarB, VarC) each having lags up to 4.

The input data set HAVE has this information (the column names are Variables and Values):

Variables Values

VarA          0

VarB          0

VarC          0

Lags          4

and the output data set WANT should be something like below (Var1, Var2, and Var3 are dynamic column names i.e. appending 1,2,3 to any string Var)

Var1     Var2      Var3

VarA     VarB      VarC 

VarA1    VarB1     VarC1

..

VarA4    VarB4     VarC4

The intention is to have this work for any number of variables in HAVE data set.

Thanks

This question doesn't make much sense to me. Can you either give a bigger picture explanation of what you're doing, or clear up what exactly is supposed to be in these datasets? Does WANT actually contain values in VarA, VarA1, etc., or is it the text "VarA1" etc.? — Joe
Joe, sorry for not being clear. Those are all just text. Basically new variable names are to be formed where the original variable names will be available from HAVE "Variables" column (except last one which is "Lags") and the number of lags will be available from VALUE column corresponding to Variable - "Lags". The last variable in the HAVE data set will always be "Lags". In the case above that means creating new variables corresponding to VarA, VarB, VarC all upto 4 lags. Just the text names at this stage are required. — user2508567

Unknown Unknown · Accepted Answer · 2013-06-24T19:58:01

The following code returns what you want. Please modify according to your needs.

/*sample input dataset*/
data have;
input Variables $ Values;
datalines;
VarA 0
VarB 0
VarC 0
Lags 4
;
run;
/*get the no. of lags form the input dataset*/
proc sql noprint;
select Values into :num_of_lags from have where upcase(variables)='LAGS';
quit;
/*transpose the input dataset such that the VarA, VarB, VarC are put in columns Var1, Var2, & Var3 respectively*/
/*have_t, the transposed dataset only has 1 row.*/
proc transpose data = have out =  have_t(drop = _name_) prefix = var;
where upcase(variables)  ne 'LAGS';
var variables;
run;
/*replicate the 1 row in have_t num_of_lags times*/
data pre_want;
    set have_t;
    array myVars{*} _character_;
        do j= 1 to &num_of_lags+1;
            do i = 1 to dim(myVars);
            myVars[i]=myVars[i];
            end;
        output;
    end;
run;
/*final dataset*/
data want;
set pre_want;
array myVars{*} _character_;
if _N_>1 then do;
do i = 1 to dim(myVars);
        myVars[i]=compress(myVars[i]!!_n_-1);
    end;
end;
drop i j;
run;
proc print data = want; run;

Output:

var1  var2  var3 
VarA  VarB  VarC 
VarA1 VarB1 VarC1 
VarA2 VarB2 VarC2 
VarA3 VarB3 VarC3 
VarA4 VarB4 VarC4

SAS - Creating combinations of different independant variables with lags

1 Answers