topic Wide to Long and calculate 12 months prior to base month in SAS Programming

Wide to Long and calculate 12 months prior to base month

Ronein — Mon, 24 Jun 2024 16:58:53 GMT

Hello

I have a data set with one row for each customer with 2 fields: CustID and YYYYMM (year month).

I want to create for each customer 12 rows with information of 12 months (till 11 months prior to YYYYMM)

What is the way ti create want data set via code that calculate it?

In real life I have 100,000 customers

Data have;
Input CustID YYYYMM;
cards;
1 202201
2 202303
;
Run;

Data want;
Input CustID Position YYYYMM;
cards;
1 0 202201
1 -1 202112
1 -2 202111
1 -3 202110
1 -4 202109
1 -5 202108
1 -6 202107
1 -7 202106
1 -8 202105
1 -9 202104
1 -10 202103
1 -11 202102
2 0 202303
2 -1 202302
2 -2 202301
2 -3 202212
2 -4 202211
2 -5 202210
2 -6 202209
2 -7 202208
2 -8 202207
2 -9 202206
2 -10 202205
2 -11 202204
;
Run;

Re: Wide to Long and calculate 12 months prior to base month

PaigeMiller — Mon, 24 Jun 2024 17:08:00 GMT

data want;
    set have;
    startyymm=input(put(yyyymm,6.),yymmn6.);    
    do i=0 to -11 by -1;
        yymm=intnx('month',startyymm,i);
        output;
    end;
    format yymm yymmn6.;
    drop i startyymm yyyymm;
run;

NOTE: Always work with valid SAS date values, the number of days since 01JAN1960. Thus, we need to convert your variable YYYYMM to an actual valid SAS date value via the INPUT function.

Re: Wide to Long and calculate 12 months prior to base month

Ronein — Mon, 24 Jun 2024 17:12:33 GMT

Great, here is the required solution by your code advice

data want (Rename=(i=Position));
    set have;
    startyymm=input(put(yyyymm,6.),yymmn6.);    
    do i=0 to -11 by -1;
        yymm=intnx('month',startyymm,i);
        output;
    end;
    format yymm yymmn6.;
    drop  startyymm yyyymm;
run;

Re: Wide to Long and calculate 12 months prior to base month

PaigeMiller — Mon, 24 Jun 2024 17:43:13 GMT

Even simpler:

data want;
    set have;
    startyymm=input(put(yyyymm,6.),yymmn6.);    
    do position=0 to -11 by -1;
        yymm=intnx('month',startyymm,position);
        output;
    end;
    format yymm yymmn6.;
    drop  startyymm yyyymm;
run;