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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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: