how to populate table with a set of continuous dates

Reply
Frequent Contributor
Posts: 129

how to populate table with a set of continuous dates

Hi,

I have a table that has dates and revenues... I want to populate it with a set of continues dates for the last 90 days and revenue field should be left blank

it should show something like this

Month               Rev

12Sep2012  

13Sep2012 

14Sep2012

...

10Dec2012

please advice how to do it

Thanks

Trusted Advisor
Posts: 1,131

Re: how to populate table with a set of continuous dates

Hi Parvaiz,

Please check the below code which will produce the output you desired.

data want;

    retain month;

    do i = 1 to 90;

    month="12SEP2012"d+i;

    revenue=.;

    output;

    end;

    format month date9.;

    drop i;

run;

proc print;

run;

i have used the do loop to generate the output.

Hope this will be helpful.

Thanks,

Jag

Thanks,
Jag
Super User
Posts: 10,516

Re: how to populate table with a set of continuous dates

Alternatively use the MONTH variable directly as the DO loop interater:

 

data want;

do month = '12SEP2012'd to ('12SEP2012'd + 89);

rev=.;

output;

end;

format month date9.;

run;

Frequent Contributor
Posts: 129

Re: how to populate table with a set of continuous dates

Sorry, I missed some additional details... in my table I already have some data so I want to APPEND dates (previous 90 days) to it, plus, my date is in a date time format...

Here is the code that I've used

data Output_TABLE3;

set inpput_TABLE2;

    do i = 1 to 90;

    DATE="12SEP2012:0:0:0"dt+i;

end;

   format DATE datetime20.;

    drop i;

run;

and obviously it's not working as I missed the details to begin with...

Thanks again for your assistance

Super Contributor
Posts: 1,636

Re: how to populate table with a set of continuous dates

Hi,

try the code below:

data want;

    do i = 1 to 90;

    DATE="12SEP2012:0:0:0"dt+i*24*3600;

output;

end;

   format DATE datetime20.;

    drop i;

run;

proc print;run;

Frequent Contributor
Posts: 129

Re: how to populate table with a set of continuous dates

Linlin,


I still don't see how your suggestion would append the data (dates for the last 90 days) to the existing table...


Thanks again for your assistance

Super Contributor
Posts: 1,636

Re: how to populate table with a set of continuous dates

can you get what you want by merging the two datasets :

data wanted;

merge want your_data; /* want is the the dataset created by my suggested code */

by date;

run;

Ask a Question
Discussion stats
  • 6 replies
  • 388 views
  • 0 likes
  • 4 in conversation