11-02-2017 04:39 AM
I'm stuck on a problem here that I hope there is a solution for somewhere in the SAS-commands.
The data looks like this:
The raw-data consists of number of cars arriving at a certain location. There is a file when they arrive (this one) and one when they leave. The data of interest is datum (date), timme (hour) and freq ) sum of number of cars arriving during that hour that date.
In the raw-data there might be some hours certain days when no cars arrive, as you can see above (hour 0, 09/02/2015 for example).
I need to match this file with the other to get the total sum of cars present every hour, taking the sum of arriving cars and subtract the leaving.
Problem is I need to have rows even when there are 0 cars arriving, not a missing row like seen above.
Is there any way I can make a loop or something similar to check if there are rows for every hour (0-23), every day (whole 2015). If the row is missing SAS will add the row with the date and hour missing with FREQ=0. Is this possible?
11-02-2017 05:12 AM
Lets start with the usual, post test data in the form of a datastep, a picture is useless to us. Also show what output you need. From what you post, I cannot see a row for 09/02/2015, nor any logic why that should be added and not 09/03/2017?
To answer you final question, yes loops are one method:
data want; set have; retain lstdt; if _n_=1 then lstdt=start_date; else do; if start_date + 1 ne lstdt then do; do i=start_date to lstdt; output; start_date=start_date+i; end; end; else output; end; run;
Something like that.
11-02-2017 09:17 AM
You can make a dummy data set of DATUM and TIMME values for 2015. Then interleave with datasets ARRIVE and LEAVE:
data date_time_dummies; do datum='01jan2015'd to '31dec2015'd; do timme=0 to 23; output; end; end; format datum mmddyy10.; run; data want (keep=datum timme total); set arrive (in=ina) leave (in=inL) date_time_dummies; by datum timme; retain total 0; if ina then total=total+_freq_; else if inL then total=total-_freq_; if last.timme; run;
11-09-2017 03:56 AM
I'm sorry for not getting back to you before. I have been away for some days but now I'm back.
We have decided to go with another solution so this case can be closed
thanks for the input though!