topic Re: Date Range between date in the same column in New SAS User

Date Range between date in the same column

hcbn — Wed, 06 Mar 2019 21:50:11 GMT

Hello

I would like to add incremental date between the dates that have qty.

Please, see the input data that I have and Output data that I would like to have. Please, help

Have

Input
DAY_DATE	qty
08Mar2019	5
15Mar2019	6
22Mar2019	4

WANT

Output
DAY_DATE	qty
08Mar2019	5
09Mar2019	0
10Mar2019	0
11Mar2019	0
12Mar2019	0
13Mar2019	0
14Mar2019	0
15Mar2019	6
16Mar2019	0
17Mar2019	0
18Mar2019	0
19Mar2019	0
20Mar2019	0
21Mar2019	0
22Mar2019	4

Re: Date Range between date in the same column

novinosrin — Wed, 06 Mar 2019 22:09:09 GMT

proc timeseries is ideal for this if you have SAS ETS

Nonetheless, Datastep can be handy



data have;
input DAY_DATE :date9.	qty;
format DAY_DATE date9.;
cards;
08Mar2019	5
15Mar2019	6
22Mar2019	4
;

data want;
set have;
retain t;
if _n_=1 then do; t=DAY_DATE;output;end;
else do;
temp=qty;
temp2=DAY_DATE;
do DAY_DATE=t to DAY_DATE-1;
qty=0;
output;
end;
qty=temp;
DAY_DATE=temp2;
output;
t=DAY_DATE;
end;
drop t:;
run;

Re: Date Range between date in the same column

hcbn — Wed, 06 Mar 2019 22:41:22 GMT

dest_id	Day_Date	qty
12305	08Mar2019	5
12305	12Mar2019	0
12305	13Mar2019	0
12305	14Mar2019	0
12305	15Mar2019	0
12305	15Mar2019	6
12305	16Mar2019	0
12305	17Mar2019	0
12305	18Mar2019	0
12305	19Mar2019	0
12305	20Mar2019	0
12305	21Mar2019	0
12305	22Mar2019	4
12913	05Mar2019	2
12913	05Mar2019	0
12913	06Mar2019	0
12913	07Mar2019	0
12913	08Mar2019	1
12913	08Mar2019	0
12913	08Mar2019	0
12913	09Mar2019	0
12913	09Mar2019	0
12913	10Mar2019	0
12913	10Mar2019	0
12913	11Mar2019	0
12913	11Mar2019	0
12913	12Mar2019	4
12913	12Mar2019	0
12913	13Mar2019	0
12913	14Mar2019	0
12913	15Mar2019	0
12913	16Mar2019	0
12913	17Mar2019	0
12913	18Mar2019	0
12913	19Mar2019	2
12913	22Mar2019	5
13690	06Mar2019	12
13690	13Mar2019	12
13827	07Mar2019	0
13827	08Mar2019	0
13827	08Mar2019	12
13827	08Mar2019	0
13827	09Mar2019	0
13827	09Mar2019	0
13827	10Mar2019	0
13827	10Mar2019	0
13827	11Mar2019	0
13827	11Mar2019	0
13827	12Mar2019	0
13827	12Mar2019	0
13827	13Mar2019	0
13827	13Mar2019	0
13827	14Mar2019	0
13827	14Mar2019	0
13827	15Mar2019	0
13827	15Mar2019	12
13827	16Mar2019	0
13827	17Mar2019	0
13827	18Mar2019	0
13827	19Mar2019	0
13827	20Mar2019	0
13827	21Mar2019	0
13827	22Mar2019	12
15007	06Mar2019	0
15007	07Mar2019	0
15007	08Mar2019	12
15007	08Mar2019	0
15007	09Mar2019	0
15007	10Mar2019	0
15007	11Mar2019	0
15007	12Mar2019	0
15007	13Mar2019	0
15007	14Mar2019	0
15007	15Mar2019	0
15007	16Mar2019	0
15007	17Mar2019	0
15007	18Mar2019	0
15007	19Mar2019	0
15007	20Mar2019	0
15007	21Mar2019	0
15007	22Mar2019	12
15211	09Mar2019	12
15469	12Mar2019	12
15469	12Mar2019	0
15469	13Mar2019	0
15469	14Mar2019	0
15469	15Mar2019	0
15469	16Mar2019	0
15469	17Mar2019	0
15469	18Mar2019	0
15469	19Mar2019	12
15916	15Mar2019	12
16355	07Mar2019	12
16355	09Mar2019	0
16355	10Mar2019	0
16355	11Mar2019	0
16355	12Mar2019	0
16355	13Mar2019	0
16355	14Mar2019	0
16355	15Mar2019	0
16355	16Mar2019	0
16355	17Mar2019	0
16355	18Mar2019	0
16355	19Mar2019	0
16355	20Mar2019	0
16355	21Mar2019	12

Re: Date Range between date in the same column

hcbn — Wed, 06 Mar 2019 22:42:03 GMT

Thank you but there are repeated dates. how we can remove them?

Re: Date Range between date in the same column

novinosrin — Wed, 06 Mar 2019 22:50:31 GMT

@hcbn let me rework. test.

Hey @hcbn Can you post a better and representative sample plz. Your sample seems to have multiple IDs?

No big deal. But I am dumb to grasp all at one

Re: Date Range between date in the same column

novinosrin — Wed, 06 Mar 2019 23:03:18 GMT

@hcbn First off, accept my apologies for not thoroughly testing.

Second of all, I have corrected the code (I think) this time

3rd of all, this should handle by groups as well

data want;
set have;
by id;
retain t;
if first.id then do; t=DAY_DATE;output;end;
else do;
temp=qty;
temp2=DAY_DATE;
do DAY_DATE=t+1 to DAY_DATE-1;
qty=0;
output;
end;
qty=temp;
DAY_DATE=temp2;
output;
t=DAY_DATE;
end;
drop t:;
run;

Re: Date Range between date in the same column

hcbn — Wed, 06 Mar 2019 23:04:41 GMT

Thank you so much for your help. There are multiple IDs. I showed my input and desired output.

Input
dest_id	source	ScheduledArrivalDate	qty
12305	88003	08Mar2019	5
12305	88003	15Mar2019	6
12305	88003	22Mar2019	4
12913	88012	05Mar2019	2
12913	88012	08Mar2019	1
12913	88012	12Mar2019	4
12913	88012	19Mar2019	2
12913	88012	22Mar2019	5
13690	88003	06Mar2019	12
13690	88003	13Mar2019	12
13827	88011	08Mar2019	12
13827	88011	15Mar2019	12
13827	88011	22Mar2019	12
15007	88011	08Mar2019	12
15007	88011	22Mar2019	12
15211	88003	09Mar2019	12
15469	88012	12Mar2019	12
15469	88012	19Mar2019	12
15916	88003	15Mar2019	12
16355	88003	07Mar2019	12
16355	88003	21Mar2019	12

Desired output for all dest_id
dest_id	source	ScheduledArrivalDate	qty
12305	88003	08Mar2019	5
12305	88003	09Mar2019	0
12305	88003	10Mar2019	0
12305	88003	11Mar2019	0
12305	88003	12Mar2019	0
12305	88003	13Mar2019	0
12305	88003	14Mar2019	0
12305	88003	15Mar2019	6
12305	88004	16Mar2019	0
12305	88005	17Mar2019	0
12305	88006	18Mar2019	0
12305	88007	19Mar2019	0
12305	88008	20Mar2019	0
12305	88009	21Mar2019	0
12305	88010	22Mar2019	4
12913	88012	05Mar2019	2
12913	88012	06Mar2019	0
12913	88012	07Mar2019	0
12913	88012	08Mar2019	1

Re: Date Range between date in the same column

novinosrin — Wed, 06 Mar 2019 23:10:40 GMT

Hi @hcbn

With your latest sample

data have;
input dest_id	source	ScheduledArrivalDate :date9.	qty;
format ScheduledArrivalDate date9.;
cards;
12305	88003	08Mar2019	5
12305	88003	15Mar2019	6
12305	88003	22Mar2019	4
12913	88012	05Mar2019	2
12913	88012	08Mar2019	1
12913	88012	12Mar2019	4
12913	88012	19Mar2019	2
12913	88012	22Mar2019	5
13690	88003	06Mar2019	12
13690	88003	13Mar2019	12
13827	88011	08Mar2019	12
13827	88011	15Mar2019	12
13827	88011	22Mar2019	12
15007	88011	08Mar2019	12
15007	88011	22Mar2019	12
15211	88003	09Mar2019	12
15469	88012	12Mar2019	12
15469	88012	19Mar2019	12
15916	88003	15Mar2019	12
16355	88003	07Mar2019	12
16355	88003	21Mar2019	12
;


data want;
set have;
by dest_id;
retain t;
if first.dest_id then do; t=ScheduledArrivalDate;output;end;
else do;
temp=qty;
temp2=ScheduledArrivalDate;
do ScheduledArrivalDate=t+1 to ScheduledArrivalDate-1;
qty=0;
output;
end;
qty=temp;
ScheduledArrivalDate=temp2;
output;
t=ScheduledArrivalDate;
end;
drop t:;
run;

Re: Date Range between date in the same column

hcbn — Wed, 06 Mar 2019 23:11:00 GMT

This one is giving Error. ERROR: Invalid DO loop control information, either the INITIAL or TO expression is missing or the BY expression is missing, zero,
or invalid.

Re: Date Range between date in the same column

novinosrin — Wed, 06 Mar 2019 23:13:56 GMT

@hcbn The test is here for the new sample

data have;
input dest_id	source	ScheduledArrivalDate :date9.	qty;
format ScheduledArrivalDate date9.;
cards;
12305	88003	08Mar2019	5
12305	88003	15Mar2019	6
12305	88003	22Mar2019	4
12913	88012	05Mar2019	2
12913	88012	08Mar2019	1
12913	88012	12Mar2019	4
12913	88012	19Mar2019	2
12913	88012	22Mar2019	5
13690	88003	06Mar2019	12
13690	88003	13Mar2019	12
13827	88011	08Mar2019	12
13827	88011	15Mar2019	12
13827	88011	22Mar2019	12
15007	88011	08Mar2019	12
15007	88011	22Mar2019	12
15211	88003	09Mar2019	12
15469	88012	12Mar2019	12
15469	88012	19Mar2019	12
15916	88003	15Mar2019	12
16355	88003	07Mar2019	12
16355	88003	21Mar2019	12
;


data want;
set have;
by dest_id;
retain t;
if first.dest_id then do; t=ScheduledArrivalDate;output;end;
else do;
temp=qty;
temp2=ScheduledArrivalDate;
do ScheduledArrivalDate=t+1 to ScheduledArrivalDate-1;
qty=0;
output;
end;
qty=temp;
ScheduledArrivalDate=temp2;
output;
t=ScheduledArrivalDate;
end;
drop t:;
run;

Re: Date Range between date in the same column

hcbn — Wed, 06 Mar 2019 23:20:18 GMT

Some IDs have desired output some does not have.

dest_id	source	qty	ScheduledArrivalDate
12305	88003	5	08Mar2019
12305	88003	6	15Mar2019
12305	88003	4	22Mar2019
12913	88012	2	05Mar2019
12913	88012	0	06Mar2019
12913	88012	0	07Mar2019
12913	88012	1	08Mar2019
12913	88012	0	08Mar2019
12913	88012	0	09Mar2019
12913	88012	0	10Mar2019
12913	88012	0	11Mar2019
12913	88012	4	12Mar2019
12913	88012	2	19Mar2019
12913	88012	5	22Mar2019
13690	88003	12	06Mar2019
13690	88003	12	13Mar2019
13827	88011	12	08Mar2019
13827	88011	12	15Mar2019
13827	88011	12	22Mar2019
15007	88011	12	08Mar2019
15007	88011	12	22Mar2019
15211	88003	12	09Mar2019
15469	88012	12	12Mar2019
15469	88012	0	13Mar2019
15469	88012	0	14Mar2019
15469	88012	0	15Mar2019
15469	88012	0	16Mar2019
15469	88012	0	17Mar2019
15469	88012	0	18Mar2019
15469	88012	12	19Mar2019
15916	88003	12	15Mar2019
16355	88003	12	07Mar2019
16355	88003	12	21Mar2019

Re: Date Range between date in the same column

novinosrin — Wed, 06 Mar 2019 23:24:27 GMT

My eyes can't spot properly. Can you mark the erroneous ones plz

Re: Date Range between date in the same column

novinosrin — Wed, 06 Mar 2019 23:30:23 GMT

Testing again:




data have;
input dest_id	source	ScheduledArrivalDate :date9.	qty;
format ScheduledArrivalDate date9.;
cards;
12305	88003	08Mar2019	5
12305	88003	15Mar2019	6
12305	88003	22Mar2019	4
12913	88012	05Mar2019	2
12913	88012	08Mar2019	1
12913	88012	12Mar2019	4
12913	88012	19Mar2019	2
12913	88012	22Mar2019	5
13690	88003	06Mar2019	12
13690	88003	13Mar2019	12
13827	88011	08Mar2019	12
13827	88011	15Mar2019	12
13827	88011	22Mar2019	12
15007	88011	08Mar2019	12
15007	88011	22Mar2019	12
15211	88003	09Mar2019	12
15469	88012	12Mar2019	12
15469	88012	19Mar2019	12
15916	88003	15Mar2019	12
16355	88003	07Mar2019	12
16355	88003	21Mar2019	12
;


data want;
set have;
by dest_id;
retain t;
if first.dest_id then do; t=ScheduledArrivalDate;output;end;
else do;
temp=qty;
temp2=ScheduledArrivalDate;
do ScheduledArrivalDate=t+1 to ScheduledArrivalDate-1;
qty=0;
output;
end;
qty=temp;
ScheduledArrivalDate=temp2;
output;
t=ScheduledArrivalDate;
end;
drop t:;
run;

proc print noobs;run;

RESULTS:

dest_id	source	ScheduledArrivalDate	qty
12305	88003	08MAR2019	5
12305	88003	09MAR2019	0
12305	88003	10MAR2019	0
12305	88003	11MAR2019	0
12305	88003	12MAR2019	0
12305	88003	13MAR2019	0
12305	88003	14MAR2019	0
12305	88003	15MAR2019	6
12305	88003	16MAR2019	0
12305	88003	17MAR2019	0
12305	88003	18MAR2019	0
12305	88003	19MAR2019	0
12305	88003	20MAR2019	0
12305	88003	21MAR2019	0
12305	88003	22MAR2019	4
12913	88012	05MAR2019	2
12913	88012	06MAR2019	0
12913	88012	07MAR2019	0
12913	88012	08MAR2019	1
12913	88012	09MAR2019	0
12913	88012	10MAR2019	0
12913	88012	11MAR2019	0
12913	88012	12MAR2019	4
12913	88012	13MAR2019	0
12913	88012	14MAR2019	0
12913	88012	15MAR2019	0
12913	88012	16MAR2019	0
12913	88012	17MAR2019	0
12913	88012	18MAR2019	0
12913	88012	19MAR2019	2
12913	88012	20MAR2019	0
12913	88012	21MAR2019	0
12913	88012	22MAR2019	5
13690	88003	06MAR2019	12
13690	88003	07MAR2019	0
13690	88003	08MAR2019	0
13690	88003	09MAR2019	0
13690	88003	10MAR2019	0
13690	88003	11MAR2019	0
13690	88003	12MAR2019	0
13690	88003	13MAR2019	12
13827	88011	08MAR2019	12
13827	88011	09MAR2019	0
13827	88011	10MAR2019	0
13827	88011	11MAR2019	0
13827	88011	12MAR2019	0
13827	88011	13MAR2019	0
13827	88011	14MAR2019	0
13827	88011	15MAR2019	12
13827	88011	16MAR2019	0
13827	88011	17MAR2019	0
13827	88011	18MAR2019	0
13827	88011	19MAR2019	0
13827	88011	20MAR2019	0
13827	88011	21MAR2019	0
13827	88011	22MAR2019	12
15007	88011	08MAR2019	12
15007	88011	09MAR2019	0
15007	88011	10MAR2019	0
15007	88011	11MAR2019	0
15007	88011	12MAR2019	0
15007	88011	13MAR2019	0
15007	88011	14MAR2019	0
15007	88011	15MAR2019	0
15007	88011	16MAR2019	0
15007	88011	17MAR2019	0
15007	88011	18MAR2019	0
15007	88011	19MAR2019	0
15007	88011	20MAR2019	0
15007	88011	21MAR2019	0
15007	88011	22MAR2019	12
15211	88003	09MAR2019	12
15469	88012	12MAR2019	12
15469	88012	13MAR2019	0
15469	88012	14MAR2019	0
15469	88012	15MAR2019	0
15469	88012	16MAR2019	0
15469	88012	17MAR2019	0
15469	88012	18MAR2019	0
15469	88012	19MAR2019	12
15916	88003	15MAR2019	12
16355	88003	07MAR2019	12
16355	88003	08MAR2019	0
16355	88003	09MAR2019	0
16355	88003	10MAR2019	0
16355	88003	11MAR2019	0
16355	88003	12MAR2019	0
16355	88003	13MAR2019	0
16355	88003	14MAR2019	0
16355	88003	15MAR2019	0
16355	88003	16MAR2019	0
16355	88003	17MAR2019	0
16355	88003	18MAR2019	0
16355	88003	19MAR2019	0
16355	88003	20MAR2019	0
16355	88003	21MAR2019	12

Re: Date Range between date in the same column

Astounding — Thu, 07 Mar 2019 00:14:32 GMT

Clearly, this problem is tricky if you require a one-step solution. Would it make sense to use a two-step solution that is much easier and clearer? For example:

data range;
   set have;
   by dest_id source ScheduledArrivalDate;
   if first.source then dates_begin = ScheduledArrivalDate;
   if last.source;
   count = 0;
   do ScheduledArrivalDate = dates_begin to ScheduledArrivalDate;
      output;
   end;
   drop dates_begin;

   retain dates_begin;
run;

That gives you a sorted data set for all the proper dates, but with COUNT=0 for all dates. Just merge back in the original counts:

data want;
    merge range have;
   by dest_id source ScheduledArrivalDate;
run;

The order of the data sets in the MERGE statement is important, to allow the valid COUNTs to overwrite the 0 COUNTs.

Re: Date Range between date in the same column

hcbn — Thu, 07 Mar 2019 04:17:42 GMT

Why do I keep receiving this error all the time?

BY variables are not properly sorted on data set

Re: Date Range between date in the same column

novinosrin — Thu, 07 Mar 2019 04:59:02 GMT

Hi @hcbn

to control this

set have;
by dest_id;

operation. the dataset requires to be sorted by dest_id.

Your sample is indeed sorted as it looks by dest_id, hence my test against your sample was rather straight forward. I trust your sample is a clean representative of your real.If not, it's difficult for me to envision or guess what your real is. I hope you had a look at the results of my test in the previous post

Re: Date Range between date in the same column

hcbn — Thu, 07 Mar 2019 05:10:42 GMT

This Is working but multiple dates

dest_id	source	qty	seqnum	ScheduledArrivalDate
12913	88012	2	1	05Mar2019
12913	88012	0	3	06Mar2019
12913	88012	0	3	07Mar2019
12913	88012	1	2	08Mar2019
12913	88012	0	3	08Mar2019
12913	88012	0	3	09Mar2019
12913	88012	0	3	10Mar2019
12913	88012	0	3	11Mar2019
12913	88012	4	3	12Mar2019
12913	88012	0	5	13Mar2019
12913	88012	0	5	14Mar2019
12913	88012	0	5	15Mar2019
12913	88012	0	5	16Mar2019
12913	88012	0	5	17Mar2019
12913	88012	0	5	18Mar2019
12913	88012	0	5	19Mar2019
12913	88012	2	4	19Mar2019

Re: Date Range between date in the same column

hcbn — Thu, 07 Mar 2019 05:13:15 GMT

The code Is working but multiple values

dest_id	source	qty	seqnum	ScheduledArrivalDate
12913	88012	2	1	05Mar2019
12913	88012	0	3	06Mar2019
12913	88012	0	3	07Mar2019
12913	88012	1	2	08Mar2019
12913	88012	0	3	08Mar2019
12913	88012	0	3	09Mar2019
12913	88012	0	3	10Mar2019
12913	88012	0	3	11Mar2019
12913	88012	4	3	12Mar2019
12913	88012	0	5	13Mar2019
12913	88012	0	5	14Mar2019
12913	88012	0	5	15Mar2019
12913	88012	0	5	16Mar2019
12913	88012	0	5	17Mar2019
12913	88012	0	5	18Mar2019
12913	88012	0	5	19Mar2019
12913	88012	2	4	19Mar2019

Re: Date Range between date in the same column

hcbn — Thu, 07 Mar 2019 05:14:39 GMT

Thank you so much for your reply. The code is working but there are duplicate dates

Re: Date Range between date in the same column

novinosrin — Thu, 07 Mar 2019 05:25:19 GMT

@hcbn

Please post me 12913 values from your sample, not the results . i'll take a look in the morning when i wake up.

The result for the sample 12913 you posted seem ok in my test against sample

12913	88012	05MAR2019	2
12913	88012	06MAR2019	0
12913	88012	07MAR2019	0
12913	88012	08MAR2019	1
12913	88012	09MAR2019	0
12913	88012	10MAR2019	0
12913	88012	11MAR2019	0
12913	88012	12MAR2019	4
12913	88012	13MAR2019	0
12913	88012	14MAR2019	0
12913	88012	15MAR2019	0
12913	88012	16MAR2019	0
12913	88012	17MAR2019	0
12913	88012	18MAR2019	0
12913	88012	19MAR2019	2
12913	88012	20MAR2019	0
12913	88012	21MAR2019	0
12913	88012	22MAR2019	5

So I am concerned if this is what is in your real. Chill for now.

Good night from Chicago. Will be in touch once I had my morning coffee