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?
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.
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;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.