cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hcbn
Obsidian | Level 7 hcbn
Obsidian | Level 7
Go to Solution

Date Range between date in the same column

Posted 03-06-2019 04:50 PM (1463 views)

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_DATEqty
08Mar20195
15Mar20196
22Mar20194

 

WANT

Output 
DAY_DATEqty
08Mar20195
09Mar20190
10Mar20190
11Mar20190
12Mar20190
13Mar20190
14Mar20190
15Mar20196
16Mar20190
17Mar20190
18Mar20190
19Mar20190
20Mar20190
21Mar20190
22Mar20194
0 Likes
1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20 novinosrin
Tourmaline | Level 20

Re: Date Range between date in the same column

Posted 03-06-2019 11:59 PM (1362 views)  |   In reply to hcbn

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

 

View solution in original post

0 Likes
36 REPLIES 36
novinosrin
Tourmaline | Level 20 novinosrin
Tourmaline | Level 20

Re: Date Range between date in the same column

Posted 03-06-2019 05:09 PM (1452 views)  |   In reply to hcbn

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;
0 Likes
hcbn
Obsidian | Level 7 hcbn
Obsidian | Level 7

Re: Date Range between date in the same column

Posted 03-06-2019 05:41 PM (1444 views)  |   In reply to novinosrin

 

 

dest_idDay_Dateqty
1230508Mar20195
1230512Mar20190
1230513Mar20190
1230514Mar20190
1230515Mar20190
1230515Mar20196
1230516Mar20190
1230517Mar20190
1230518Mar20190
1230519Mar20190
1230520Mar20190
1230521Mar20190
1230522Mar20194
1291305Mar20192
1291305Mar20190
1291306Mar20190
1291307Mar20190
1291308Mar20191
1291308Mar20190
1291308Mar20190
1291309Mar20190
1291309Mar20190
1291310Mar20190
1291310Mar20190
1291311Mar20190
1291311Mar20190
1291312Mar20194
1291312Mar20190
1291313Mar20190
1291314Mar20190
1291315Mar20190
1291316Mar20190
1291317Mar20190
1291318Mar20190
1291319Mar20192
1291322Mar20195
1369006Mar201912
1369013Mar201912
1382707Mar20190
1382708Mar20190
1382708Mar201912
1382708Mar20190
1382709Mar20190
1382709Mar20190
1382710Mar20190
1382710Mar20190
1382711Mar20190
1382711Mar20190
1382712Mar20190
1382712Mar20190
1382713Mar20190
1382713Mar20190
1382714Mar20190
1382714Mar20190
1382715Mar20190
1382715Mar201912
1382716Mar20190
1382717Mar20190
1382718Mar20190
1382719Mar20190
1382720Mar20190
1382721Mar20190
1382722Mar201912
1500706Mar20190
1500707Mar20190
1500708Mar201912
1500708Mar20190
1500709Mar20190
1500710Mar20190
1500711Mar20190
1500712Mar20190
1500713Mar20190
1500714Mar20190
1500715Mar20190
1500716Mar20190
1500717Mar20190
1500718Mar20190
1500719Mar20190
1500720Mar20190
1500721Mar20190
1500722Mar201912
1521109Mar201912
1546912Mar201912
1546912Mar20190
1546913Mar20190
1546914Mar20190
1546915Mar20190
1546916Mar20190
1546917Mar20190
1546918Mar20190
1546919Mar201912
1591615Mar201912
1635507Mar201912
1635509Mar20190
1635510Mar20190
1635511Mar20190
1635512Mar20190
1635513Mar20190
1635514Mar20190
1635515Mar20190
1635516Mar20190
1635517Mar20190
1635518Mar20190
1635519Mar20190
1635520Mar20190
1635521Mar201912
0 Likes
hcbn
Obsidian | Level 7 hcbn
Obsidian | Level 7

Re: Date Range between date in the same column

Posted 03-06-2019 05:42 PM (1443 views)  |   In reply to hcbn

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

0 Likes
novinosrin
Tourmaline | Level 20 novinosrin
Tourmaline | Level 20

Re: Date Range between date in the same column

Posted 03-06-2019 05:48 PM (1439 views)  |   In reply to hcbn

@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

0 Likes
hcbn
Obsidian | Level 7 hcbn
Obsidian | Level 7

Re: Date Range between date in the same column

Posted 03-06-2019 06:04 PM (1428 views)  |   In reply to novinosrin

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

Input   
dest_idsourceScheduledArrivalDateqty
123058800308Mar20195
123058800315Mar20196
123058800322Mar20194
129138801205Mar20192
129138801208Mar20191
129138801212Mar20194
129138801219Mar20192
129138801222Mar20195
136908800306Mar201912
136908800313Mar201912
138278801108Mar201912
138278801115Mar201912
138278801122Mar201912
150078801108Mar201912
150078801122Mar201912
152118800309Mar201912
154698801212Mar201912
154698801219Mar201912
159168800315Mar201912
163558800307Mar201912
163558800321Mar201912

 

 

 

Desired output for all dest_id 
dest_idsourceScheduledArrivalDateqty
123058800308Mar20195
123058800309Mar20190
123058800310Mar20190
123058800311Mar20190
123058800312Mar20190
123058800313Mar20190
123058800314Mar20190
123058800315Mar20196
123058800416Mar20190
123058800517Mar20190
123058800618Mar20190
123058800719Mar20190
123058800820Mar20190
123058800921Mar20190
123058801022Mar20194
129138801205Mar20192
129138801206Mar20190
129138801207Mar20190
129138801208Mar20191
0 Likes
novinosrin
Tourmaline | Level 20 novinosrin
Tourmaline | Level 20

Re: Date Range between date in the same column

Posted 03-06-2019 06:10 PM (1424 views)  |   In reply to hcbn

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;
0 Likes
novinosrin
Tourmaline | Level 20 novinosrin
Tourmaline | Level 20

Re: Date Range between date in the same column

Posted 03-06-2019 06:03 PM (1429 views)  |   In reply to hcbn

@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;
0 Likes
hcbn
Obsidian | Level 7 hcbn
Obsidian | Level 7

Re: Date Range between date in the same column

Posted 03-06-2019 06:11 PM (1423 views)  |   In reply to novinosrin

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.

0 Likes
novinosrin
Tourmaline | Level 20 novinosrin
Tourmaline | Level 20

Re: Date Range between date in the same column

Posted 03-06-2019 06:13 PM (1416 views)  |   In reply to hcbn

@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;

 

0 Likes
hcbn
Obsidian | Level 7 hcbn
Obsidian | Level 7

Re: Date Range between date in the same column

Posted 03-06-2019 06:20 PM (1411 views)  |   In reply to novinosrin

Some IDs have desired output some does not have.

 

dest_idsourceqtyScheduledArrivalDate
1230588003508Mar2019
1230588003615Mar2019
1230588003422Mar2019
1291388012205Mar2019
1291388012006Mar2019
1291388012007Mar2019
1291388012108Mar2019
1291388012008Mar2019
1291388012009Mar2019
1291388012010Mar2019
1291388012011Mar2019
1291388012412Mar2019
1291388012219Mar2019
1291388012522Mar2019
13690880031206Mar2019
13690880031213Mar2019
13827880111208Mar2019
13827880111215Mar2019
13827880111222Mar2019
15007880111208Mar2019
15007880111222Mar2019
15211880031209Mar2019
15469880121212Mar2019
1546988012013Mar2019
1546988012014Mar2019
1546988012015Mar2019
1546988012016Mar2019
1546988012017Mar2019
1546988012018Mar2019
15469880121219Mar2019
15916880031215Mar2019
16355880031207Mar2019
16355880031221Mar2019
0 Likes
novinosrin
Tourmaline | Level 20 novinosrin
Tourmaline | Level 20

Re: Date Range between date in the same column

Posted 03-06-2019 06:24 PM (1406 views)  |   In reply to hcbn

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

0 Likes
novinosrin
Tourmaline | Level 20 novinosrin
Tourmaline | Level 20

Re: Date Range between date in the same column

Posted 03-06-2019 06:30 PM (1402 views)  |   In reply to novinosrin

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
0 Likes
hcbn
Obsidian | Level 7 hcbn
Obsidian | Level 7

Re: Date Range between date in the same column

Posted 03-06-2019 11:17 PM (1368 views)  |   In reply to novinosrin

Why do I keep receiving this error all the time?

BY variables are not properly sorted on data set

0 Likes
novinosrin
Tourmaline | Level 20 novinosrin
Tourmaline | Level 20

Re: Date Range between date in the same column

Posted 03-06-2019 11:59 PM (1363 views)  |   In reply to hcbn

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

 

0 Likes

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Watch Mastering the WHERE Clause in PROC SQL on YouTube

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 36 replies
  • ‎03-06-2019 04:50 PM
  • 1464 views
  • 2 likes
  • 3 in conversation