Turn on suggestions

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

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- Possible to add a row to dataset for missing values?

Options

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 11-02-2017 04:39 AM
(2274 views)

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

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

--------------------------

The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for

Allow PROC SORT to output multiple datasets

--------------------------

The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for

Allow PROC SORT to output multiple datasets

--------------------------

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

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. **

What is Bayesian Analysis?

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.