topic Re: to make values repeat on dates in SAS Programming

to make values repeat on dates

kz134 — Thu, 18 Oct 2018 20:40:24 GMT

Hi I have this dataset. The date column is always the last day of the month.

Customer Type	Date	Value
A	31-Aug-18	100
B	31-Aug-18	120
A	31-Jul-18	80

I want this output. I want the value column to repeat for all dates in that month.

Customer Type	Date	Value
A	01-Aug-18	100
A	02-Aug-18	100
A	03-Aug-18	100
A	04-Aug-18	100
A	05-Aug-18	100
…	…	…
B	01-Aug-18	120
B	02-Aug-18	120
B	03-Aug-18	120
B	04-Aug-18	120
…	…	…
A	01-Jul-18	80
A	02-Jul-18	80
A	03-Jul-18	80
A	04-Jul-18	80
…	…	…

Thanks for your help

Re: to make values repeat on dates

PeterClemmensen — Thu, 18 Oct 2018 20:48:42 GMT

Do like this

data have;
input Customer_Type $ Date:date9. Value;
format Date date9.;
datalines;
A 31-Aug-18 100
B 31-Aug-18 120
A 31-Jul-18 80
;

data want(drop=dt);
   set have(rename=(Date=dt));
   do dt=intnx('Month', dt, 0, 'b') to dt;
      Date=dt;
      output;
   end;
   format Date date9.;
run;

Re: to make values repeat on dates

kz134 — Thu, 18 Oct 2018 20:56:40 GMT

Thank you. This worked.

I understand the do loop. And I understand we created a new variable called DT. I don't understand why we have date=dt. What does that do?

Re: to make values repeat on dates

novinosrin — Thu, 18 Oct 2018 20:59:47 GMT

Hi @kz134 While i fully agree with @PeterClemmensen, the code can be written as

data have;
input Customer_Type $ Date:date9. Value;
format Date date9.;
datalines;
A 31-Aug-18 100
B 31-Aug-18 120
A 31-Jul-18 80
;

data want;
   set have;
   do date=intnx('Month', date, 0, 'b') to date;
      output;
   end;
run;

The idea is to thoroughly understand the compilation process

Re: to make values repeat on dates

data_null__ — Thu, 18 Oct 2018 22:29:16 GMT

I would suggest to use INTNX for the stop value too. Also suggest this great paper on the subject of DOing things.

http://support.sas.com/resources/papers/proceedings13/126-2013.pdf

data want;
   set have;
   do date=intnx('Month',date,0,'b') to intnx('Month',date,0,'e');
      output;
      end;
   run;

Re: to make values repeat on dates

novinosrin — Thu, 18 Oct 2018 22:32:59 GMT

Great nit. Noted Boss. Thank you again. Please get used to the phrase "Thank you". 🙂