Hi- I have a table with ID, start year month, end year month and sales per month like below:
ID | st_yrmo | end_yrmo | salespermo |
a | 201701 | 201703 | 12.9 |
b | 201801 | 201804 | 12.5 |
c | 201705 | 201706 | 13.8 |
c | 201710 | 201712 | 12.2 |
For each ID for the range, I would like to have a table like below which has ID, month and sales per month for each month as per table above.
ID | yrmo | salespermo |
a | 201701 | 12.9 |
a | 201702 | 12.9 |
a | 201703 | 12.9 |
b | 201801 | 12.5 |
b | 201802 | 12.5 |
b | 201803 | 12.5 |
b | 201804 | 12.5 |
c | 201705 | 13.8 |
c | 201706 | 13.8 |
c | 201710 | 12.2 |
c | 201711 | 12.2 |
c | 201712 | 12.2 |
data have;
infile cards expandtabs truncover;
input ID $ (st_yrmo end_yrmo) (:yymmn6.) salespermo;
format st_yrmo end_yrmo yymmn6.;
cards;
a 201701 201703 12.9
b 201801 201804 12.5
c 201705 201706 13.8
c 201710 201712 12.2
;
data want;
set have;
do while(st_yrmo<=end_yrmo);
output;
st_yrmo=intnx('month',st_yrmo,1);
end;
run;
Are the dates in your original table character or numeric?
If numeric, please indicate if the first value in the first row is the integer 201701 or is it a real SAS date value formatted as YYMM6.
data have;
infile cards expandtabs truncover;
input ID $ (st_yrmo end_yrmo) (:yymmn6.) salespermo;
format st_yrmo end_yrmo yymmn6.;
cards;
a 201701 201703 12.9
b 201801 201804 12.5
c 201705 201706 13.8
c 201710 201712 12.2
;
data want;
set have;
do while(st_yrmo<=end_yrmo);
output;
st_yrmo=intnx('month',st_yrmo,1);
end;
run;
Thanks a lot. This worked perfect for my purpose.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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: