BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
imanojkumar1
Quartz | Level 8
 

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?

1 ACCEPTED SOLUTION

Accepted Solutions
user24feb
Barite | Level 11

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

4 REPLIES 4
Astounding
PROC Star

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.

imanojkumar1
Quartz | Level 8

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.

user24feb
Barite | Level 11

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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