Grouping ID variable by date to create daily time series

[ngrannum75]

Hi, 

I have a table that has Prod_Ids (product avail to sell) and Start_date and End_date. I would like to group similar Prod_Ids for each day ... essentially creating a count of Prod_Ids (product) avail to sell each day. For example, Prod_Ids = 1011111, Count = 50, Start_date = 01JUN2020. So I am also changing the datetime to date9. 

My key question is how to count the Prod_Ids for each day to create a daily count of product Prod_IDs. 

Here is an example of the data: 

Prod_Id	Start_date	End_date
10644800	26FEB2020:04:05:50.180000	27FEB2020:04:25:54.189999
10656210	02SEP2019:04:15:36.840000	04SEP2019:04:03:47.819999
10656210	07MAR2020:06:25:11.780000	08MAR2020:06:37:29.919999
10656210	17FEB2020:03:55:08.130000	18FEB2020:04:58:05.809999
10665400	21AUG2019:12:44:09.780000	24AUG2019:05:16:01.809999
10665400	06NOV2019:05:51:53.420000	11NOV2019:05:14:50.279999
10682600	27FEB2020:04:25:54.190000	28FEB2020:04:25:13.529999
10703611	29SEP2019:04:04:38.320000	30SEP2019:05:19:45.929999
10703611	15DEC2019:04:12:49.910000	21DEC2019:04:40:18.419999
10703611	16NOV2019:05:19:58.700000	17NOV2019:04:43:56.199999
10703611	21AUG2019:12:44:09.780000	24AUG2019:05:16:01.809999
10703611	06NOV2019:05:51:53.420000	11NOV2019:05:14:50.279999

[ballardw]

It might help a lot if you start by showing the count for one of the Prod_id values in the shown data.

From what we see there are exactly 0 anything for Prod_id = 1011111.

One way would be to create one record for each Prod_id date combination. Your data implies there are no overlaps in the start/end periods for a given Prod_id. Is that actually true? If so then:

data need;
   set have;
   do date = datepart(start_date) to datepart(end_date);
      output;
   end;
   keep prod_id date;
   format date date9.;
run;

proc freq data=need;
   tables date;
run;

Caution: if you have lots of Prod_id values with lots of long intervals you might end up with a largish Need data set and take a noticeable amount of time.

I make absolutely no claim to efficiency of time though the code itself is short. If you need a data set then direct the output of the tables statement to a data set with the /out=wantedsetname option.

[Jagadishkatam]

Could you please try the below code and I believe that you don't want to expand the number of rows per ID, but just need the count in days for every id and start date, so you get the count variable something like in the screenshot. Please explain if i did not understand your query.

data have;
input Prod_Id	Start_date:datetime18.6	End_date:datetime18.6	;
cards;
10644800 26FEB2020:04:05:50.180000 27FEB2020:04:25:54.189999
10656210 02SEP2019:04:15:36.840000 04SEP2019:04:03:47.819999
10656210 07MAR2020:06:25:11.780000 08MAR2020:06:37:29.919999
10656210 17FEB2020:03:55:08.130000 18FEB2020:04:58:05.809999
10665400 21AUG2019:12:44:09.780000 24AUG2019:05:16:01.809999
10665400 06NOV2019:05:51:53.420000 11NOV2019:05:14:50.279999
10682600 27FEB2020:04:25:54.190000 28FEB2020:04:25:13.529999
10703611 29SEP2019:04:04:38.320000 30SEP2019:05:19:45.929999
10703611 15DEC2019:04:12:49.910000 21DEC2019:04:40:18.419999
10703611 16NOV2019:05:19:58.700000 17NOV2019:04:43:56.199999
10703611 21AUG2019:12:44:09.780000 24AUG2019:05:16:01.809999
10703611 06NOV2019:05:51:53.420000 11NOV2019:05:14:50.279999
;

data want;
set have;
by Prod_Id Start_date notsorted;
new_Start_date=datepart(Start_date);
new_End_date=datepart(End_date);
format new_Start_date new_End_date i date9.;
do i=new_Start_date to new_End_date;
count=(new_End_date-new_Start_date)+1;
end;
drop i;
run;