Creating a Cumulative Dataset

[xxartpopxx]

Hello, I am trying to show a table where one column has the dates of the month and the second column has the number of eligible people. Eligibility is defined by those who have an admin_date_1 (date variable) greater than or equal to 28 days later. The number of eligible people in the column needs to be cumulative meaning it should add up at the end of September to the total number of eligible people in the entire dataset. For example if I have 5 people in my dataset and someone is eligible on the 3rd and another person is eligible on the 7th there should be 2 people eligible and then at the end of the month there should be 5 people.

This is the code I have so far. I am stuck on intnx variable! Do I need it? How can I show all the dates in the month and show a cumulative number of eligible people per day in the month?

data dose2eligible;
set request;

/*date_eligible=admin_dt_1 +28 ; /*number eligible on the date*/
date_eligible=intnx('day',admin_dt_1+28,30);

month_revisit=month(date_eligible); /*month of revisiting*/
output;
format
month_revisit date_eligible date9.;
run;

/*determine amount eligible*/
proc freq data=dose2eligible; where month_revisit=9 ; table date_eligible; run;

[mkeintz]

Please provide sample data, in the form of a working data step, what you starting data looks like.  And show what the  corresponding resulting data/report should look like.

I for one need this information just to understand what you are asking to be done.  As it is now,  I don't get it.

[xxartpopxx]

The starting data just has the admin_1_date, and their record_ids.

With the code below I was able to create sept1st-sept30th dates. But I'm struggling now to add another column for those who have an eligible admin date. If dosediff=>28 then they are eligible. I'm also unsure how to cumulate the eligible numbers for the entire month of september.

data dose2eligible;
set request;
/*create September 1st to September 30th date*/
date='01SEP2022'd;
dosediff=date-admin_dt_1;
do i=1 to 30 by 1;
if i=1 then
date=date;
else date=date+1;
output;
end;
format
date date9.;

run;

[andreas_lds]

Please post the data you have in usable form and the expected result. And please use "insert sas code" to post formatted code.

[Kurt_Bremser]

This

do i=1 to 30 by 1;
if i=1 then
date=date;
else date=date+1;
output;
end;

is much easier done in this way:

do date = '01sep2022'd to '30sep2022'd;
  output;
end;

But to understand what you want to do, we must see your existing dataset "request" in usable form, and what you want to get out of it.

[xxartpopxx]

Example Dataset:

record_id admin_dt_1  

1                June 7th 2022

2                August 25th 2022

3                August 23rd 2022

4                 July 8th 2022

5                  August 5th 2022

I would like my output to show in the first column September 1st...2nd...so on to 30th which I have done but I would like the second column to  show the number of people eligible for each day in September. Eligible means anyone after 28 days from their admin_dt_1. I also want the column to be cumulative it should look something like this: Since there are 5 data points it should add up to 5 in the frequency column.

Date            Frequency eligible

September 1st    3

September 30th  5

[Kurt_Bremser]

Since you did not post usable example data as requested, I have to assume that admin_dt_1 is a SAS date and not some funny-looking string.

So you first need to expand the dates:

data want;
set have;
do elig_date = admin_dt_1 to admin_dt_1 + 29;
  output;
end;
format elig_date yymmdd10.;
drop admin_dt_1;
run;

and then you can run PROC FREQ on elig_date.