topic Re: Convert date range in two columns into long table in SAS Programming

Convert date range in two columns into long table

AviS — Thu, 23 Jan 2020 14:59:46 GMT

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

Re: Convert date range in two columns into long table

PaigeMiller — Thu, 23 Jan 2020 15:04:58 GMT

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.

Re: Convert date range in two columns into long table

novinosrin — Thu, 23 Jan 2020 15:43:19 GMT

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;

Re: Convert date range in two columns into long table

AviS — Thu, 23 Jan 2020 18:18:26 GMT

Thanks a lot. This worked perfect for my purpose.