Date column should change based on the date range that I give. Please, look at the Input and the output. The date will start 03/03/2019 in the output because I want the output to start from 03/03/2019 and finish on 03/23/2019. However, qty should match the date in the input with the output.
Input | |||
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 |
dest_id | source | ScheduledArrivalDate | qty |
12305 | 87998 | 03Mar2019 | 0 |
12305 | 87999 | 04Mar2019 | 0 |
12305 | 88000 | 05Mar2019 | 0 |
12305 | 88001 | 06Mar2019 | 0 |
12305 | 88002 | 07Mar2019 | 0 |
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 |
12305 | 88003 | 23Mar2019 | 0 |
12913 | 88010 | 03Mar2019 | 0 |
12913 | 88011 | 04Mar2019 | 0 |
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 |
12913 | 88012 | 23Mar2019 | 0 |
@hcbn Got it.
But in your required output data, where would I find the information for source column values as this is not available in your input data.
Suppose I run a loop with your specified date range and look up the input , for the non matches, it's easy to assign qty=0 but what about source?
dest_id | source | ScheduledArrivalDate | qty |
12305 | 87998 | 03Mar2019 | 0 |
12305 | 87999 | 04Mar2019 | 0 |
12305 | 88000 | 05Mar2019 | 0 |
12305 | 88001 | 06Mar2019 | 0 |
12305 | 88002 | 07Mar2019 | 0 |
Logic is the same as the previous result but the data should start from the date we required? I am sorry. The Source did not change. Please, disregard the source. Please, look at the dates between the input and the output.
Input | |||
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 |
DESIRED OUTPUT | |||||
dest_id | source | ScheduledArrivalDate | qty | ||
12305 | 88003 | 03Mar2019 | 0 | It will start from 3/3/2019 | |
12305 | 88003 | 04Mar2019 | 0 | ||
12305 | 88003 | 05Mar2019 | 0 | ||
12305 | 88003 | 06Mar2019 | 0 | ||
12305 | 88003 | 07Mar2019 | 0 | ||
12305 | 88003 | 08Mar2019 | 5 | 5 pcs correspond to qty as it was in the input 3/8/2019 | |
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 | ||
12305 | 88003 | 23Mar2019 | 0 | it will end 3/23/2019 | |
@hcbn Here you go
/*Creating HAVE Sample */
data have ;
input dest_id source ScheduledArrivalDate :date9. qty;
format ScheduledArrivalDate date9.;
cards;
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
;
;
/*Creating user defined marco vars to request as per need*/
%let startdate='03mar2019'd;
%let enddate='23mar2019'd;
data want;
if _n_=1 then do;
if 0 then set have;
dcl hash H (dataset:'have') ;
h.definekey ("dest_id","ScheduledArrivalDate") ;
h.definedata ("ScheduledArrivalDate", "qty") ;
h.definedone () ;
end;
set have(keep=dest_id source);
by dest_id;
if first.dest_id;
do ScheduledArrivalDate=&startdate to &enddate;
if h.find() ne 0 then qty=0;
output;
end;
run;
You are the best ever. It is working like a clock. Thank you so much. I really appreciate your great help.
Pleasure is all mine. Have a good day!
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.