03-24-2016 02:39 PM
I have the following data set:
for each paired record (where paired = first and second), i'm trying to get the field in car2 to output in the observation above it so when car1 has a value, car2 has the value from below. it should look like this:
i am having the hardest time thinking of how to output this way. would it be something with _n_+1? nobs? my code is below:
set work.filtered_hu nobs=nobs;
by date_stopped time train_id car_pos;
if firstrecord=1 and lastrecord=1 then paired = "single";
if firstrecord=1 and lastrecord=0 then paired = "first car";
if firstrecord=0 and lastrecord=1 then paired = "second car";
if paired = "first" then car1=catx(" ",carint,number);
if paired = "single" then car1=catx(" ",carint,number);
if paired = "second" then car2=catx(" ",series,car_number);
if paired = "single" then car2=catx(" ",series,car_number);
03-24-2016 02:48 PM
Looking back is called lag, looking forward is called lead.
There's some good posts on here on how to access the next record.
You're probably not going to get code if you post pictures as your data, SAS doesn't read pictures and I'm not typing in your data.
You can create sample data for the forum by following the instructions here:
And here's a bunch of resources that show how to access lead.
A part of me also wonders if you can't go back a step and clean it up easier.
EDIT: Realized your transposing the data from the variable CAR_MARK - use transpose and merge back is also another method.
03-24-2016 03:04 PM
No...apparently the wrong link posted...search does work though
03-24-2016 09:16 PM
Are you sure the paired obs will always appear ? And don't post your data as picture, post it as text ,better be SAS code. Nobody will like to type it for you ,if you want solution. data have; input paired $ car1 $ car2 $; cards; first X . second . Y single Z . ; run; data want; merge have have(keep=paired car1 car2 rename=(paired=_paired car1=_car1 car2=_car2) firstobs=2); if paired='first' and _paired='second' then do; car1=coalescec(car1,_car1); car2=coalescec(car2,_car2); end; if lag(paired)='first' and paired='second' then call missing(car1,car2); drop _:; run;