topic How do I add observations for consecutive days in a SAS dataset? in SAS Programming

How do I add observations for consecutive days in a SAS dataset?

raajdesaii — Thu, 24 Dec 2020 16:43:16 GMT

Hello everyone,

I have a dataset that looks like this:

ID	Date	Days
1	20JAN2011	5
1	3FEB2011	4
1	10MAR2011	1
2	8AUG2011	10
3	12DEC2011	29
4	5JAN2013	21
5	1 MAR2014	14

I want to add new observations by ID based on the number of days. How do I get this output?

ID	Date	Days
1	20JAN2011	5
1	21JAN2011	5
1	22JAN2011	5
1	23JAN2011	5
1	24JAN2011	5
1	25JAN2011	5
1	3FEB2011	4
1	4FEB2011	4
1	5FEB2011	4
1	6FEB2011	4

and so on.....

Re: How do I add observations for consecutive days in a SAS dataset?

PaigeMiller — Thu, 24 Dec 2020 17:19:10 GMT

Are your dates actual numeric SAS dates? Or are they character strings?

Why does you output have 6 records from the first observation and 4 records from the second observation?

Re: How do I add observations for consecutive days in a SAS dataset?

raajdesaii — Thu, 24 Dec 2020 17:25:29 GMT

They are actual numeric SAS dates.

The first patient has 6 records where as the second patient only has 4 records and so on. Date is the variable which indicates when they started the drug and days indicates for how many dates. So patient 1 started the first treatment on a particular date for x number of days. Same patient started another treatment on another date for x number of days. I just want to add all the days to the date so I get the dataset as I have posted.

Re: How do I add observations for consecutive days in a SAS dataset?

PaigeMiller — Thu, 24 Dec 2020 18:59:21 GMT

If the first patient had 5 records in your output, and the second patient had 4 records in your output, then your explanation makes sense because days for the first patient is 5 and days for the second patient has 4.

But that's not what your output shows. Please explain why the first patient has 6 and the second patient has 4.

Re: How do I add observations for consecutive days in a SAS dataset?

PGStats — Thu, 24 Dec 2020 19:20:38 GMT

data want;
set have;
do i = 1 to days;
    output;
    date = intnx("day", date, 1);
    end;
drop i;
run;

Re: How do I add observations for consecutive days in a SAS dataset?

raajdesaii — Thu, 24 Dec 2020 19:37:31 GMT

My bad, that is just an error.

ID	Date	Days
1	20JAN2011	5
1	21JAN2011	5
1	22JAN2011	5
1	23JAN2011	5
1	24JAN2011	5
1	3FEB2011	4
1	4FEB2011	4
1	5FEB2011	4
1	6FEB2011	4

Re: How do I add observations for consecutive days in a SAS dataset?

raajdesaii — Thu, 24 Dec 2020 20:00:23 GMT

I tried using this but it did not work, it would add the same date to all the id's. I want it to look like this:

ID Date Days
1 20JAN2011 5
1 21JAN2011 5
1 22JAN2011 5
1 23JAN2011 5
1 24JAN2011 5
1 3FEB2011 4
1 4FEB2011 4
1 5FEB2011 4
1 6FEB2011 4

Re: How do I add observations for consecutive days in a SAS dataset?

PGStats — Thu, 24 Dec 2020 21:36:42 GMT

Works for me:

data have;
input ID  Date :date9.   Days;
format date yymmdd10.;
datalines;
1   20JAN2011   5
1   3FEB2011    4
1   10MAR2011   1
2   8AUG2011    10
3   12DEC2011   29
4   5JAN2013    21
5   1MAR2014   14
;

data want;
set have;
do i = 1 to days;
    output;
    date = intnx("day", date, 1);
    end;
drop i;
run;

proc print noobs data=want; run;

....

Re: How do I add observations for consecutive days in a SAS dataset?

raajdesaii — Thu, 24 Dec 2020 22:04:35 GMT

That worked! I had made a small error. Thanks!