Help using Base SAS procedures

Possible to add a row to dataset for missing values?

Reply
New Contributor
Posts: 2

Possible to add a row to dataset for missing values?

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:

 

Skärmavbild 2017-11-02 kl. 09.26.32.jpg

 

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

Super User
Super User
Posts: 9,599

Re: Possible to add a row to dataset for missing values?

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.

Trusted Advisor
Posts: 1,345

Re: Possible to add a row to dataset for missing values?

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;
New Contributor
Posts: 2

Re: Possible to add a row to dataset for missing values?

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 Smiley Happy

 

thanks for the input though!

 

Regards

Peter Karlsson

Ask a Question
Discussion stats
  • 3 replies
  • 362 views
  • 0 likes
  • 3 in conversation