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:
TPMC | PWC | PWSC | Site | ET | Date | Time | DIAM | PXMC | SF |
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 |
So the Output should look like this:
TPMC | PWC | PWSC | Site | ET | Date | Time | DIAM | PXMC | SF |
03Nov2016 | |||||||||
04Nov2016 | |||||||||
05Nov2016 | |||||||||
06Nov2016 | |||||||||
07Nov2016 | |||||||||
7101 | 7101 | US000521 | 1 | Works1 | 08Nov2016 | 11:58 | 890,3 | 1 | |
09Nov2016 | |||||||||
10Nov2016 | |||||||||
11Nov2016 | |||||||||
12Nov2016 | |||||||||
13Nov2016 | |||||||||
28Oct2016 | |||||||||
29Oct2016 | |||||||||
30Oct2016 | |||||||||
31Oct2016 | |||||||||
01Nov2016 | |||||||||
7102 | 7102 | US000361 | 1 | Works1 | 02Nov2016 | 13:01 | 878,1 | 1 | |
03Nov2016 | |||||||||
04Nov2016 | |||||||||
05Nov2016 | |||||||||
06Nov2016 | |||||||||
07Nov2016 |
and so forth.
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;
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.
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;
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:
TPMC | PWC | PWSC | Site | ET | Date | Time | DIAM | PXMC | SF |
7101 | 03Nov2016 | 0:00 | |||||||
7101 | 04Nov2016 | 0:00 | |||||||
7101 | 05Nov2016 | 0:00 | |||||||
7101 | 06Nov2016 | 0:00 | |||||||
7101 | 07Nov2016 | 0:00 | |||||||
7101 | 7101 | US000521 | 1 | Works1 | 08Nov2016 | 11:58 | 890,3 | 1 | |
7101 | 09Nov2016 | 0:00 | |||||||
7101 | 10Nov2016 | 0:00 | |||||||
7101 | 11Nov2016 | 0:00 | |||||||
7101 | 12Nov2016 | 0:00 | |||||||
7101 | 13Nov2016 | 0:00 | |||||||
7102 | 28Oct2016 | 0:00 | |||||||
7102 | 29Oct2016 | 0:00 | |||||||
7102 | 30Oct2016 | 0:00 | |||||||
7102 | 31Oct2016 | 0:00 | |||||||
7102 | 01Nov2016 | 0:00 | |||||||
7102 | 7102 | US000361 | 1 | Works1 | 02Nov2016 | 13:01 | 878,1 | 1 | |
7102 | 03Nov2016 | 0:00 | |||||||
7102 | 04Nov2016 | 0:00 | |||||||
7102 | 05Nov2016 | 0:00 | |||||||
7102 | 06Nov2016 | 0:00 | |||||||
7102 | 07Nov2016 | 0:00 |
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;
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:
TopParentMachCode | MachCode | EventType | Date | Time | Site | Diameter | ParentXMachCode | MileageValue | service_flag |
US000328 | Lathing | 02JAN16 | 0:00 | 885.9 | 123124.12 | 1 | |||
US000328 | Lathing | 13JUN16 | 0:00 | 1 | 881.4 | LI1147P | 3213.43 | 1 | |
US000329 | Lathing | 14JAN16 | 0:00 | 872.6 | 342424.12 | 1 | |||
US000329 | Lathing | 05AUG16 | 0:00 | 863.1 | 1 | ||||
7114 | US000331 | Installation | 19APR16 | 0:00 | 3 | . | LI1147P | 1 | |
US000331 | Lathing | 12JUN16 | 0:00 | 860.7 | 12313.54 | 1 | |||
US000334 | Lathing | 24JAN16 | 0:00 | 853.2 | 212122.45 | 1 |
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.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.