Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 87
Accepted Solution

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

 

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?


Accepted Solutions
Solution
‎11-11-2016 04:17 AM
Super Contributor
Posts: 336

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

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;

View solution in original post


All Replies
Grand Advisor
Posts: 17,308

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

Post your exact log please. 

Respected Advisor
Posts: 4,955

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

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.

Frequent Contributor
Posts: 87

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

[ Edited ]

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.

Solution
‎11-11-2016 04:17 AM
Super Contributor
Posts: 336

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

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 313 views
  • 3 likes
  • 4 in conversation