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

Question

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?

You need to show what you've tried so far, and ask a specific question about some area where you're having trouble. Is VAR3 a character var? If so, you'll need the INPUT function to convert it to a date. You can then use the INTNX function to find the appropriate dates 1 month, 2 months and 3 months earlier: blogs.sas.com/content/iml/2017/05/15/… — Chris Long
could you elaborate a bit more? It seems that you have a dataset with 3 variables, but 3rd paragraph seems to indicate that each yyyymm line is pointing to individual dataset. Which is it? — pinegulf
So what do these other datasets look like? How did you create them with names like '201704' which are not valid for use by SAS as a member name? — Tom
Other datasets also looks the same. Its just kind of a monthly dataset of similar format. 201704 is a dateformat yymmn6. — Lekshmi Vijayakumar
It looks VAR1 is the variable name and VAR3 is the dataset name (or at least could be converted into the dataset name). But what is the purpose of VAR2 in the example data you posted? Do you want to compare the value to the values pulled from the monthly datasets? — Tom

pinegulf pinegulf · Accepted Answer · 2017-07-17T12:05:01

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.

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

1 Answers