topic Re: Create a variable number of new lines by and ID dependent on months to a certain date in SAS Programming

Create a variable number of new lines by and ID dependent on months to a certain date

pfdjadsfdsafas — Wed, 06 Mar 2019 19:35:15 GMT

First post here, so sorry about formatting/missing details.

I have a data set that is just a numeric ID and a month value that is a string in format YYYY-M (e.g. September 2017 would be 2017-9). All of the values are in 2017 or 2018. I need to create a row for each ID for each month from the value provided up to December 2018. I also have these dates in a raw date format as well if that would make this easier.

So for example, the input

5134223 2018-10

Would need to output

5134223 2018-10 

5134223 2018-11 

5134223 2018-12

^having trouble with the formatting here but these should be on 3 separate lines.

If anyone can provide any help that would be amazing. Again, I can get these dates in a normal date format but the way I have been approaching it was going to use it as a string until I got stuck.

Re: Create a variable number of new lines by and ID dependent on months to a certain date

Shmuel — Wed, 06 Mar 2019 21:35:13 GMT

Few hints:

1) separate year (informat 4.) from month (informat best2.);

2) Create sas date using function MDY() - startDate = mdy(month, 01, year);

3) Use DO loop UNTIL (date = '01DEC2018'd); advance date by INTNX() function:

date = intnx('month', date,1);

4) output each date created in the loop.

Re: Create a variable number of new lines by and ID dependent on months to a certain date

DanielLangley — Wed, 06 Mar 2019 21:55:18 GMT

You can use either the string date or the number date. The trick is turning the string date into a number date 🙂

data work.have;
label id = "ID";
informat Rawdate date9.;
format Rawdate NowNumberdate yymmd7.;
length StringDate $7;
input id Rawdate StringDate $;

NowNumberdate = input(  compress(stringdate,,'kd') , yymmn6. );

datalines;
5134223 1OCT2018 2018-10
;
run;



data work.want;
set work.have;
output;
do until ( coalesce(NowNumberdate,"1dec2018"d) ge "1dec2018"d);
	NowNumberdate = intnx('month', NowNumberdate,1);
		output;
end;
run;