turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Possible to add a row to dataset for missing value...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-02-2017 04:39 AM

Hello,

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?

Regards

Peter Karlsson

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to PeterK

11-02-2017 05:12 AM

Hi,

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to PeterK

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to mkeintz

11-09-2017 03:56 AM

Hi!

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!

Regards

Peter Karlsson