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 addional rows with empty data in all the columns except for Date column, which should get dates 5 days before and 5 days after the each Date in the following dataset:

 

TPMCPWCPWSCSiteETDateTimeDIAMPXMCSF
71017101US0005211Works108Nov201611:58890,3 1
71027102US0003611Works202Nov201613:01878,1 1
71027102UC0003482Works307Nov201618:22877,3 1
71067106UC004241Works105Oct20169:43890,4 1
71067106UC004373Works207Nov201618:23877,1 1
71067106UC3094Works307Nov201618:26877,8 1
71077107UC053271Works106Oct20168:41837 1
71077107UC2002Works213Oct201612:53890,55 1
71087108UC0003613Works102Nov201613:01878,1 1
71087108UC004321Works207Nov201618:25877,8 1
71087108UC1062Works303Oct20169:37890,3 1

 

 

So the Output should look like this:

 

TPMCPWCPWSCSiteETDateTimeDIAMPXMCSF
     03Nov2016    
     04Nov2016    
     05Nov2016    
     06Nov2016    
     07Nov2016    
71017101US0005211Works108Nov201611:58890,3 1
     09Nov2016    
     10Nov2016    
     11Nov2016    
     12Nov2016    
     13Nov2016    
     28Oct2016    
     29Oct2016    
     30Oct2016    
     31Oct2016    
     01Nov2016    
71027102US0003611Works102Nov201613:01878,1 1
     03Nov2016    
     04Nov2016    
     05Nov2016    
     06Nov2016    
     07Nov2016    

 

and so forth.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

data have(index=(date));
infile cards truncover expandtabs;
input TPMC	PWC	PWSC $	Site	ET	$ Date : date9.	Time $ DIAM	$ PXMC	$ SF;
format date date9.;
cards;
7101	7101	US000521	1	Works1	08Nov2016	11:58	890,3	 	1
7102	7102	US000361	1	Works2	02Nov2016	13:01	878,1	 	1
7102	7102	UC000348	2	Works3	07Nov2016	18:22	877,3	 	1
7106	7106	UC00424	1	Works1	05Oct2016	9:43	890,4	 	1
7106	7106	UC00437	3	Works2	07Nov2016	18:23	877,1	 	1
7106	7106	UC309	4	Works3	07Nov2016	18:26	877,8	 	1
7107	7107	UC05327	1	Works1	06Oct2016	8:41	837	 	1
7107	7107	UC200	2	Works2	13Oct2016	12:53	890,55	 	1
7108	7108	UC000361	3	Works1	02Nov2016	13:01	878,1	 	1
7108	7108	UC00432	1	Works2	07Nov2016	18:25	877,8	 	1
7108	7108	UC106	2	Works3	03Oct2016	9:37	890,3	 	1
;
run;

data want;
 set have;
 array x{1} _temporary_;
 x{1}=date;
 call missing(of _all_);
 do i=5 to 1 by -1;
   date=x{1}-i;output;
 end;
 set have point=_n_;output;
 call missing(of _all_);
 do i=1 to 5;
   date=x{1}+i;output;
 end;
 drop i;
 run;


View solution in original post

8 REPLIES 8
Kurt_Bremser
Super User

You will get a multitude of completely identical observations (same date, all other variables missing). These are usually a major PITA to work with.

I'd rather have at least another column (TPMC,PWC?) populated so you can build groups and preserve the correct order.

 

 

 

Ksharp
Super User

data have(index=(date));
infile cards truncover expandtabs;
input TPMC	PWC	PWSC $	Site	ET	$ Date : date9.	Time $ DIAM	$ PXMC	$ SF;
format date date9.;
cards;
7101	7101	US000521	1	Works1	08Nov2016	11:58	890,3	 	1
7102	7102	US000361	1	Works2	02Nov2016	13:01	878,1	 	1
7102	7102	UC000348	2	Works3	07Nov2016	18:22	877,3	 	1
7106	7106	UC00424	1	Works1	05Oct2016	9:43	890,4	 	1
7106	7106	UC00437	3	Works2	07Nov2016	18:23	877,1	 	1
7106	7106	UC309	4	Works3	07Nov2016	18:26	877,8	 	1
7107	7107	UC05327	1	Works1	06Oct2016	8:41	837	 	1
7107	7107	UC200	2	Works2	13Oct2016	12:53	890,55	 	1
7108	7108	UC000361	3	Works1	02Nov2016	13:01	878,1	 	1
7108	7108	UC00432	1	Works2	07Nov2016	18:25	877,8	 	1
7108	7108	UC106	2	Works3	03Oct2016	9:37	890,3	 	1
;
run;

data want;
 set have;
 array x{1} _temporary_;
 x{1}=date;
 call missing(of _all_);
 do i=5 to 1 by -1;
   date=x{1}-i;output;
 end;
 set have point=_n_;output;
 call missing(of _all_);
 do i=1 to 5;
   date=x{1}+i;output;
 end;
 drop i;
 run;


imanojkumar1
Quartz | Level 8

Hi

 

Just a quick question.

 

If I want TPMC value to be repeated in those rows where 5 before dates and 5 after dates are, how can we do it? Also instead of empty cell, for Time, can it be 00:00 ?

 

Something like this as end result:

 

TPMCPWCPWSCSiteETDateTimeDIAMPXMCSF
7101    03Nov20160:00   
7101    04Nov20160:00   
7101    05Nov20160:00   
7101    06Nov20160:00   
7101    07Nov20160:00   
71017101US0005211Works108Nov201611:58890,3 1
7101    09Nov20160:00   
7101    10Nov20160:00   
7101    11Nov20160:00   
7101    12Nov20160:00   
7101    13Nov20160:00   
7102    28Oct20160:00   
7102    29Oct20160:00   
7102    30Oct20160:00   
7102    31Oct20160:00   
7102    01Nov20160:00   
71027102US0003611Works102Nov201613:01878,1 1
7102    03Nov20160:00   
7102    04Nov20160:00   
7102    05Nov20160:00   
7102    06Nov20160:00   
7102    07Nov20160:00   
Ksharp
Super User

Sure.

 


data have(index=(date));
infile cards truncover expandtabs;
input TPMC	PWC	PWSC $	Site	ET	$ Date : date9.	Time : hhmmss5. DIAM	$ PXMC	$ SF;
format date date9. time hhmm5.;
cards;
7101	7101	US000521	1	Works1	08Nov2016	11:58	890,3	 	1
7102	7102	US000361	1	Works2	02Nov2016	13:01	878,1	 	1
7102	7102	UC000348	2	Works3	07Nov2016	18:22	877,3	 	1
7106	7106	UC00424	1	Works1	05Oct2016	9:43	890,4	 	1
7106	7106	UC00437	3	Works2	07Nov2016	18:23	877,1	 	1
7106	7106	UC309	4	Works3	07Nov2016	18:26	877,8	 	1
7107	7107	UC05327	1	Works1	06Oct2016	8:41	837	 	1
7107	7107	UC200	2	Works2	13Oct2016	12:53	890,55	 	1
7108	7108	UC000361	3	Works1	02Nov2016	13:01	878,1	 	1
7108	7108	UC00432	1	Works2	07Nov2016	18:25	877,8	 	1
7108	7108	UC106	2	Works3	03Oct2016	9:37	890,3	 	1
;
run;

data want;
 set have;
 array x{2} _temporary_;
 x{1}=date;x{2}=TPMC;
 call missing(of _all_);
 TPMC=x{2};Time=0;
 do i=5 to 1 by -1;
   date=x{1}-i;output;
 end;
 set have point=_n_;output;
 call missing(of _all_);
 TPMC=x{2};Time=0;
 do i=1 to 5;
   date=x{1}+i;output;
 end;
 drop i;
 run;
imanojkumar1
Quartz | Level 8

Hello Ksharp,

 

Thanks for helping me. Please ignore the variables names and data (disclosure restrictions at office), i applied your method on my data and I got the following errors:

 

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      20:13   
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
      22:20   28:20   
ERROR: Array subscript out of range at line 20 column 13.
MachCode=US0001 EventType=Lathing Date=02JAN13 Time=19:24 TopPMachCode=  ParentXMachCode=  Site=  Diameter=876
MileageValue=2660403.00000 service_flag=1 i=. _ERROR_=1 _N_=1
NOTE: The SAS System stopped processing this step because of errors.

 

What could be the reason?

 

 

Sample Data:

 

TopParentMachCodeMachCodeEventTypeDateTimeSiteDiameterParentXMachCodeMileageValueservice_flag
 US000328Lathing02JAN160:00 885.9 123124.121
 US000328Lathing13JUN160:001881.4LI1147P3213.431
 US000329Lathing14JAN160:00 872.6 342424.121
 US000329Lathing05AUG160:00 863.1  1
7114US000331Installation19APR160:003.LI1147P 1
 US000331Lathing12JUN160:00 860.7 12313.541
 US000334Lathing24JAN160:00 853.2 212122.451
Kurt_Bremser
Super User

You failed to properly populate the variable i (see i=. in the list of values after the ERROR).

If you used i to index into the array, that's the reason for the ERROR.

And look at program lines 20, 22 and 28 to get rid of the automatic type casts. Especially line 20, where the subscript out of range also happens, will be suspicious.

That's all we can tell from here without seeing the code.

Ksharp
Super User
I guess TPMC is character type variable , so you need character type array.


data want;
 set have;
 array x{1} _temporary_;
array y{1} $ 40 _temporary_;
 x{1}=date;y{1}=TPMC;
 call missing(of _all_);
 TPMC=y{1};Time=0;
 do i=5 to 1 by -1;
   date=x{1}-i;output;
 end;
 set have point=_n_;output;
 call missing(of _all_);
 TPMC=y{1};Time=0;
 do i=1 to 5;
   date=x{1}+i;output;
 end;
 drop i;
 run;

imanojkumar1
Quartz | Level 8
Yes this is correct. I got my mistake. Thanks for correcting. I got to learn a lot from this. and Finally this solution worked! 🙂

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
  • 8 replies
  • 1180 views
  • 2 likes
  • 3 in conversation