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

Re: Date Range between date in the same column

Posted 03-07-2019 12:13 AM (917 views)  |   In reply to novinosrin

The code Is working but multiple values

 

dest_idsourceqtyseqnumScheduledArrivalDate
12913880122105Mar2019
12913880120306Mar2019
12913880120307Mar2019
12913880121208Mar2019
12913880120308Mar2019
12913880120309Mar2019
12913880120310Mar2019
12913880120311Mar2019
12913880124312Mar2019
12913880120513Mar2019
12913880120514Mar2019
12913880120515Mar2019
12913880120516Mar2019
12913880120517Mar2019
12913880120518Mar2019
12913880120519Mar2019
12913880122419Mar2019
0 Likes
hcbn
Obsidian | Level 7 hcbn
Obsidian | Level 7

Re: Date Range between date in the same column

Posted 03-07-2019 12:14 AM (916 views)  |   In reply to novinosrin

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

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

Re: Date Range between date in the same column

Posted 03-07-2019 12:25 AM (914 views)  |   In reply to hcbn

@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

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

Re: Date Range between date in the same column

Posted 03-07-2019 12:10 AM (917 views)  |   In reply to novinosrin

This Is working but multiple dates

 

 

dest_idsourceqtyseqnumScheduledArrivalDate
12913880122105Mar2019
12913880120306Mar2019
12913880120307Mar2019
12913880121208Mar2019
12913880120308Mar2019
12913880120309Mar2019
12913880120310Mar2019
12913880120311Mar2019
12913880124312Mar2019
12913880120513Mar2019
12913880120514Mar2019
12913880120515Mar2019
12913880120516Mar2019
12913880120517Mar2019
12913880120518Mar2019
12913880120519Mar2019
12913880122419Mar2019
0 Likes
hcbn
Obsidian | Level 7 hcbn
Obsidian | Level 7

Re: Date Range between date in the same column

Posted 03-07-2019 09:39 AM (899 views)  |   In reply to novinosrin

Can you please look at 1,2,3? The code does not work for them

 dest_idsourceqtyScheduledArrivalDate
11230588003508Mar2019
21230588003615Mar2019
31230588003422Mar2019
41291388012205Mar2019
51291388012006Mar2019
61291388012007Mar2019
71291388012108Mar2019
81291388012008Mar2019
91291388012009Mar2019
101291388012010Mar2019
111291388012011Mar2019
121291388012412Mar2019
131291388012013Mar2019
141291388012014Mar2019
151291388012015Mar2019
161291388012016Mar2019
171291388012017Mar2019
181291388012018Mar2019
191291388012019Mar2019
201291388012219Mar2019
211291388012020Mar2019
221291388012021Mar2019
231291388012522Mar2019
2413690880031206Mar2019
251369088003007Mar2019
261369088003008Mar2019
271369088003009Mar2019
281369088003010Mar2019
291369088003011Mar2019
301369088003012Mar2019
3113690880031213Mar2019
3213827880111208Mar2019
331382788011009Mar2019
341382788011010Mar2019
351382788011011Mar2019
361382788011012Mar2019
371382788011013Mar2019
381382788011014Mar2019
3913827880111215Mar2019
4013827880111222Mar2019
4115007880111208Mar2019
421500788011009Mar2019
431500788011010Mar2019
441500788011011Mar2019
451500788011012Mar2019
461500788011013Mar2019
471500788011014Mar2019
481500788011015Mar2019
491500788011016Mar2019
501500788011017Mar2019
511500788011018Mar2019
521500788011019Mar2019
531500788011020Mar2019
541500788011021Mar2019
5515007880111222Mar2019
5615211880031209Mar2019
5715469880121212Mar2019
581546988012013Mar2019
591546988012014Mar2019
601546988012015Mar2019
611546988012016Mar2019
621546988012017Mar2019
631546988012018Mar2019
6415469880121219Mar2019
6515916880031215Mar2019
6616355880031207Mar2019
671635588003008Mar2019
681635588003009Mar2019
691635588003010Mar2019
701635588003011Mar2019
711635588003012Mar2019
721635588003013Mar2019
731635588003014Mar2019
741635588003015Mar2019
751635588003016Mar2019
761635588003017Mar2019
771635588003018Mar2019
781635588003019Mar2019
791635588003020Mar2019
8016355880031221Mar2019
0 Likes
novinosrin
Tourmaline | Level 20 novinosrin
Tourmaline | Level 20

Re: Date Range between date in the same column

Posted 03-07-2019 09:41 AM (897 views)  |   In reply to hcbn

Sorry, what do you mean by 1,2,3?

obs no? 

qty?

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

Re: Date Range between date in the same column

Posted 03-07-2019 09:42 AM (894 views)  |   In reply to novinosrin

first 1 , 2 and 3 observation

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

Re: Date Range between date in the same column

Posted 03-07-2019 09:49 AM (889 views)  |   In reply to hcbn

Hi @hcbn   Tested again, same code

 

Please review the below carefully. Do feel free to come back with any number of questions.

 

 

 


/*Creating HAVE Sample with qty=0 fromy your previous post*/
data have have1;
input obsno dest_id	source	qty		ScheduledArrivalDate :date9.;
if qty ne 0 then output have;
output have1;
format ScheduledArrivalDate date9.;
cards;
1	12305	88003	5	08Mar2019
2	12305	88003	6	15Mar2019
3	12305	88003	4	22Mar2019
4	12913	88012	2	05Mar2019
5	12913	88012	0	06Mar2019
6	12913	88012	0	07Mar2019
7	12913	88012	1	08Mar2019
8	12913	88012	0	08Mar2019
9	12913	88012	0	09Mar2019
10	12913	88012	0	10Mar2019
11	12913	88012	0	11Mar2019
12	12913	88012	4	12Mar2019
13	12913	88012	0	13Mar2019
14	12913	88012	0	14Mar2019
15	12913	88012	0	15Mar2019
16	12913	88012	0	16Mar2019
17	12913	88012	0	17Mar2019
18	12913	88012	0	18Mar2019
19	12913	88012	0	19Mar2019
20	12913	88012	2	19Mar2019
21	12913	88012	0	20Mar2019
22	12913	88012	0	21Mar2019
23	12913	88012	5	22Mar2019
24	13690	88003	12	06Mar2019
25	13690	88003	0	07Mar2019
26	13690	88003	0	08Mar2019
27	13690	88003	0	09Mar2019
28	13690	88003	0	10Mar2019
29	13690	88003	0	11Mar2019
30	13690	88003	0	12Mar2019
31	13690	88003	12	13Mar2019
32	13827	88011	12	08Mar2019
33	13827	88011	0	09Mar2019
34	13827	88011	0	10Mar2019
35	13827	88011	0	11Mar2019
36	13827	88011	0	12Mar2019
37	13827	88011	0	13Mar2019
38	13827	88011	0	14Mar2019
39	13827	88011	12	15Mar2019
40	13827	88011	12	22Mar2019
41	15007	88011	12	08Mar2019
42	15007	88011	0	09Mar2019
43	15007	88011	0	10Mar2019
44	15007	88011	0	11Mar2019
45	15007	88011	0	12Mar2019
46	15007	88011	0	13Mar2019
47	15007	88011	0	14Mar2019
48	15007	88011	0	15Mar2019
49	15007	88011	0	16Mar2019
50	15007	88011	0	17Mar2019
51	15007	88011	0	18Mar2019
52	15007	88011	0	19Mar2019
53	15007	88011	0	20Mar2019
54	15007	88011	0	21Mar2019
55	15007	88011	12	22Mar2019
56	15211	88003	12	09Mar2019
57	15469	88012	12	12Mar2019
58	15469	88012	0	13Mar2019
59	15469	88012	0	14Mar2019
60	15469	88012	0	15Mar2019
61	15469	88012	0	16Mar2019
62	15469	88012	0	17Mar2019
63	15469	88012	0	18Mar2019
64	15469	88012	12	19Mar2019
65	15916	88003	12	15Mar2019
66	16355	88003	12	07Mar2019
67	16355	88003	0	08Mar2019
68	16355	88003	0	09Mar2019
69	16355	88003	0	10Mar2019
70	16355	88003	0	11Mar2019
71	16355	88003	0	12Mar2019
72	16355	88003	0	13Mar2019
73	16355	88003	0	14Mar2019
74	16355	88003	0	15Mar2019
75	16355	88003	0	16Mar2019
76	16355	88003	0	17Mar2019
77	16355	88003	0	18Mar2019
78	16355	88003	0	19Mar2019
79	16355	88003	0	20Mar2019
80	16355	88003	12	21Mar2019
;

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;

RESULTS:

dest_id source qty ScheduledArrivalDate
12305 88003 5 08MAR2019
12305 88003 0 09MAR2019
12305 88003 0 10MAR2019
12305 88003 0 11MAR2019
12305 88003 0 12MAR2019
12305 88003 0 13MAR2019
12305 88003 0 14MAR2019
12305 88003 6 15MAR2019
12305 88003 0 16MAR2019
12305 88003 0 17MAR2019
12305 88003 0 18MAR2019
12305 88003 0 19MAR2019
12305 88003 0 20MAR2019
12305 88003 0 21MAR2019
12305 88003 4 22MAR2019
12913 88012 2 05MAR2019
12913 88012 0 06MAR2019
12913 88012 0 07MAR2019
12913 88012 1 08MAR2019
12913 88012 0 09MAR2019
12913 88012 0 10MAR2019
12913 88012 0 11MAR2019
12913 88012 4 12MAR2019
12913 88012 0 13MAR2019
12913 88012 0 14MAR2019
12913 88012 0 15MAR2019
12913 88012 0 16MAR2019
12913 88012 0 17MAR2019
12913 88012 0 18MAR2019
12913 88012 2 19MAR2019
12913 88012 0 20MAR2019
12913 88012 0 21MAR2019
12913 88012 5 22MAR2019
13690 88003 12 06MAR2019
13690 88003 0 07MAR2019
13690 88003 0 08MAR2019
13690 88003 0 09MAR2019
13690 88003 0 10MAR2019
13690 88003 0 11MAR2019
13690 88003 0 12MAR2019
13690 88003 12 13MAR2019
13827 88011 12 08MAR2019
13827 88011 0 09MAR2019
13827 88011 0 10MAR2019
13827 88011 0 11MAR2019
13827 88011 0 12MAR2019
13827 88011 0 13MAR2019
13827 88011 0 14MAR2019
13827 88011 12 15MAR2019
13827 88011 0 16MAR2019
13827 88011 0 17MAR2019
13827 88011 0 18MAR2019
13827 88011 0 19MAR2019
13827 88011 0 20MAR2019
13827 88011 0 21MAR2019
13827 88011 12 22MAR2019
15007 88011 12 08MAR2019
15007 88011 0 09MAR2019
15007 88011 0 10MAR2019
15007 88011 0 11MAR2019
15007 88011 0 12MAR2019
15007 88011 0 13MAR2019
15007 88011 0 14MAR2019
15007 88011 0 15MAR2019
15007 88011 0 16MAR2019
15007 88011 0 17MAR2019
15007 88011 0 18MAR2019
15007 88011 0 19MAR2019
15007 88011 0 20MAR2019
15007 88011 0 21MAR2019
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 0 08MAR2019
16355 88003 0 09MAR2019
16355 88003 0 10MAR2019
16355 88003 0 11MAR2019
16355 88003 0 12MAR2019
16355 88003 0 13MAR2019
16355 88003 0 14MAR2019
16355 88003 0 15MAR2019
16355 88003 0 16MAR2019
16355 88003 0 17MAR2019
16355 88003 0 18MAR2019
16355 88003 0 19MAR2019
16355 88003 0 20MAR2019
16355 88003 12 21MAR2019

 

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

Re: Date Range between date in the same column

Posted 03-07-2019 10:08 AM (886 views)  |   In reply to novinosrin

I am really sorry that I took your time so much but this code also does not work.

1,2 and 3 obs are not working with code

 

dest_idsourceqtyScheduledArrivalDate
1230588003422Mar2019
1230588003615Mar2019
1230588003508Mar2019
1291388012108Mar2019
1291388012205Mar2019
1291388012006Mar2019
1291388012007Mar2019
1291388012008Mar2019
1291388012009Mar2019
1291388012010Mar2019
1291388012011Mar2019
0 Likes
novinosrin
Tourmaline | Level 20 novinosrin
Tourmaline | Level 20

Re: Date Range between date in the same column

Posted 03-07-2019 10:30 AM (875 views)  |   In reply to hcbn

@hcbn   Don't worry. We have to work interactively. Are you sure the samples you post are alike what you have?

In the below one, your ScheduledArrivalDate is not sorted or in other words it is properly ordered in ascending or descending order.

First, you need to clarify

1. is it okay to 

sort by dest_id ScheduledArrivalDate;

If yes, the order would be like all ScheduledArrivalDate sorted in ascending order within each dest_id

So  

proc sort data=have out=sorted_have;

by dest_id ScheduledArrivalDate;

run;

2. Once sort is done, the WANT datastep using the sorted_have to get the desired output will work. 

 

Take your time. Please do clarify the above.

Post a comprehensive sample of your HAVE and the expected OUTPUT. 

 

I will have a coffee in the meantime

 

dest_id source qty ScheduledArrivalDate
12305 88003 4 22Mar2019
12305 88003 6 15Mar2019
12305 88003 5 08Mar2019
12913 88012 1 08Mar2019
12913 88012 2 05Mar2019
12913 88012 0 06Mar2019
12913 88012 0 07Mar2019
12913 88012 0 08Mar2019
12913 88012 0 09Mar2019
12913 88012 0 10Mar2019
12913 88012 0 11Mar2019
0 Likes
hcbn
Obsidian | Level 7 hcbn
Obsidian | Level 7

Re: Date Range between date in the same column

Posted 03-07-2019 10:51 AM (870 views)  |   In reply to novinosrin

I do not know how to thank you. It is working excellent. Thank you so much. The code is working. I did your advice. 

 


Proc sort data=work.daily_pln_arriv out= work.daily_pln_arriv;
by dest_id ScheduledArrivalDate;
run;


data work.xxxx;
set work.daily_pln_arriv;/*have*/
by dest_id;
retain t;
if first.dest_id then do; t=ScheduledArrivalDate;output;end;
else do;
format t temp2 ScheduledArrivalDate date9.;
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-07-2019 10:54 AM (867 views)  |   In reply to hcbn

Hi @hcbn   LOL. Let's thank each other. 🙂  

 

Have a good day.

 

 

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

Re: Date Range between date in the same column

Posted 03-07-2019 11:01 AM (862 views)  |   In reply to novinosrin

@novinosrin 

hcbn
Obsidian | Level 7 hcbn
Obsidian | Level 7

Re: Date Range between date in the same column

Posted 03-07-2019 11:58 AM (854 views)  |   In reply to hcbn

@novinosrin 

 

Can I ask a different question? Can I add date range into the data like this below?

Date range is between  03/03/2019 and 03/23/2019.

Input   
dest_idsourceScheduledArrivalDateqty
123058800308Mar20195
123058800309Mar20190
123058800310Mar20190
123058800311Mar20190
123058800312Mar20190
123058800313Mar20190
123058800314Mar20190
123058800315Mar20196
123058800316Mar20190
123058800317Mar20190
123058800318Mar20190
123058800319Mar20190
123058800320Mar20190
123058800321Mar20190
123058800322Mar20194
129138801205Mar20192
129138801206Mar20190
129138801207Mar20190
129138801208Mar20191
129138801209Mar20190
129138801210Mar20190
129138801211Mar20190
129138801212Mar20194
129138801213Mar20190
129138801214Mar20190
129138801215Mar20190
129138801216Mar20190
129138801217Mar20190
129138801218Mar20190
129138801219Mar20192
129138801220Mar20190
129138801221Mar20190
129138801222Mar20195

 

DESIRED OUTPUT  
dest_idsourceScheduledArrivalDateqty
123058799601Mar20190
123058799702Mar20190
123058799803Mar20190
123058799904Mar20190
123058800005Mar20190
123058800106Mar20190
123058800207Mar20190
123058800308Mar20195
123058800309Mar20190
123058800310Mar20190
123058800311Mar20190
123058800312Mar20190
123058800313Mar20190
123058800314Mar20190
123058800315Mar20196
123058800316Mar20190
123058800317Mar20190
123058800318Mar20190
123058800319Mar20190
123058800320Mar20190
123058800321Mar20190
123058800322Mar20194
123058800323Mar20190
129138801003Mar20190
129138801104Mar20190
129138801205Mar20192
129138801206Mar20190
129138801207Mar20190
129138801208Mar20191
129138801209Mar20190
129138801210Mar20190
129138801211Mar20190
129138801212Mar20194
129138801213Mar20190
129138801214Mar20190
129138801215Mar20190
129138801216Mar20190
129138801217Mar20190
129138801218Mar20190
129138801219Mar20192
129138801220Mar20190
129138801221Mar20190
129138801222Mar20195
129138801223Mar20190
0 Likes
novinosrin
Tourmaline | Level 20 novinosrin
Tourmaline | Level 20

Re: Date Range between date in the same column

Posted 03-07-2019 12:05 PM (852 views)  |   In reply to hcbn

 Can I add date range into the data like this below?

Date range is between  03/03/2019 and 03/23/2019.

 

Hi @hcbn   the question seems little incomplete. 

 

1. Do you want to add a filter i.e do you wanna subset or something?

2. If yes, for All Id's?

3. What's the objective? Can i add is something not clear to me

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
  • 1461 views
  • 2 likes
  • 3 in conversation