topic Re: How to add continuous dates in the column - a range of dates as impute in SAS Enterprise Guide

How to add continuous dates in the column - a range of dates as impute

imanojkumar1 — Tue, 08 Nov 2016 14:47:34 GMT

I want to add continuous dates in the column 'Date' for each of unique value in the column 'V1'. And below explains what I am doing and what error i am getting (unable to impute dates).

I have a table as shown below:

V1          Date
MS000328    04JAN16
MS000328    08JAN16
MS000328    12JAN16
MS000328    08FEB16
MS000328    09FEB16
MS000328    12MAR16
MS000328    15MAR16
MS000328    16APR16
MS000328    17APR16
MS000329    02JAN16
MS000329    16JAN16
MS000329    29JAN16
MS000329    03FEB16
MS000329    19FEB16
MS000329    21MAR16
MS000329    31MAR16
MS000329    11APR16
MS000329    19APR16

What I want something like this:

V1          Date
MS000328    01JAN16
MS000328    02JAN16
MS000328    03JAN16
MS000328    04JAN16
MS000328    05JAN16
MS000328    06JAN16
MS000328    07JAN16
MS000328    08JAN16
.
.
.
.
.
MS000329    01JAN16
MS000329    02JAN16
MS000329    03MAR16
.
.
MS000329    01APR16
MS000329    02APR16
.
.
MS000329    01MAR16
MS000329    02MAR16
.
.
MS000329    01APR16
MS000329    02APR16
.
.
.

I am using the follwing code....

data want1 (keep = V1 Date);
   set have;
   by V1;
run;

data want;
   set want1;
   format Date date.;
   Date = "1JAN2016"d;
   do i = 0 to 365;
   Date+1;
   output;
   end;
 run;

The result log shows an error... 'Insufficient space in file'

What I did as wrong?

Re: How to add continuous dates in the column - a range of dates as impute

Reeza — Tue, 08 Nov 2016 14:58:53 GMT

Post your exact log please.

Re: How to add continuous dates in the column - a range of dates as impute

Astounding — Tue, 08 Nov 2016 15:05:37 GMT

What you did was wrong because it generates 366 observations for each one of your original observations. When your first V1 has 9 observations, this does not generate 366 observations. Instead, it generates (9 x 366) observations.

Your logic is fine. You just need to start with a data set that contains one observation for each V1.

And you need more disk space. Even generating 9 times as many observation as are needed should not be able to use up all the available disk space.

Re: How to add continuous dates in the column - a range of dates as impute

imanojkumar1 — Tue, 08 Nov 2016 15:21:17 GMT

Woh ! Yes This is I never thought of. Thanks for the suggestion about disk space. I got to be a lot careful. Need to rethink about this data munging. But I am just confused. Need help on it. Thanks.

Re: How to add continuous dates in the column - a range of dates as impute

user24feb — Tue, 08 Nov 2016 15:30:00 GMT

I believe you are trying to do something like this (and I agree with Astounding that it is not certain that you really will run out of memory):

Data Have;
  Input V1 $ Date Date9.;
  Format Date Date9.;
  Datalines;
MS000328    04JAN16
MS000328    08JAN16
MS000328    12JAN16
MS000328    08FEB16
MS000328    09FEB16
MS000328    12MAR16
MS000328    15MAR16
MS000328    16APR16
MS000328    17APR16
MS000329    02JAN16
MS000329    16JAN16
MS000329    29JAN16
MS000329    03FEB16
MS000329    19FEB16
MS000329    21MAR16
MS000329    31MAR16
MS000329    11APR16
MS000329    19APR16
;
Run;

* List of Dates;
Data Dates (Keep=Date);
  Format Date Date9.;
  Do i=0 To 365;
    Date=IntNX('day','01JAN2016'd,i);
	Output;
  End;
Run;

* List of V1s;
Proc SQL;
  Create Table V1_IDs As Select Distinct V1 From Have Order By V1;
Quit;

* Full structure;
Proc SQL;
  Create Table Want As Select * From V1_IDs,Dates;
Quit;