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;
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.
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.
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;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: