CODEWITHSUNDEEP
sas-macro
Lekshmi Vijayakumar
Lekshmi Vijayakumar

Reputation: 1

SAS - how to extract multiple datasets based on a dynamic date variable in another dataset?

I have a master dataset A:

Var1   Var2   Var3
X      50     201704
Y      100    201705
Z      150    201704

Var1 - String Var2 - Numeric Var3 - Date yymmn6.

I want to extract var2 from previous 3 months datasets (similar histpry datasets) for all these records.

Say, for X and Z i need var2 variable from datasets of 201703, 201702 & 201701. And for Y i need var2 from datasets of 201704, 201703 and 201702.

And then i have to calculate the sum of all the var2 for each observation.

I believe it's a sas macro work. I am not able to show my code since the data is much more complicated.

Can someone please help in getting this cracked?

Upvotes: 0

Views: 353

Answers (1)

pinegulf
pinegulf

Reputation: 1396

I hope this could help with the first bit: 

data set_A; 
    input Var1 $ Var2 Var3;
    cards;
    X 50 201704 
    Y 100 201705 
    Z 150 201704
    ; 
run;

/I want to extract var2 from previous 3 months datasets for all these records./

data set_A; 
    set set_A; 
    Y=substr( left(var3),1,4);
    M=substr( left(var3),5,2);
    date_var = mdy(M, '01', Y);
run;

data Var2_last3_M;
    set set_A;
    if date_var >= date()-30*3; /*You can use intck function here too*/
    keep = var2;
run;

Edit: Based on feedback, this is what I came up with: 

%macro Get_month_data(year, month);
    data wanted&year._&month.; 
        set set_a;
        if year(date_var)=&year and 
            month(date_var)=&month.; /*adjust as needed see below*/
    run;
%mend Get_month_data;
%Get_month_data(2017,4); /*Apr of 2017 data */
%Get_month_data(2017,5); /*May data*/

Since you said you wanted month backwards data the logic could be something like: 

(month(date_var)=&month. or month(date_var)=%eval(&month.-1) );

Adjust as you require.

Upvotes: 0

Related Questions