topic Entering Second Observation into Previous Observation Column in SAS Programming

Entering Second Observation into Previous Observation Column

laneylaners — Thu, 24 Mar 2016 18:39:56 GMT

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:

data work.want;
set work.filtered_hu nobs=nobs;
by date_stopped time train_id car_pos;
firstrecord=first.TIME;
lastrecord=last.TIME;

***define pairings***;
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";

***define car1***;
if paired = "first" then car1=catx(" ",carint,number);
if paired = "single" then car1=catx(" ",carint,number);

***define car2***;
if paired = "second" then car2=catx(" ",series,car_number);
if paired = "single" then car2=catx(" ",series,car_number);

run;

Re: Entering Second Observation into Previous Observation Column

Reeza — Thu, 24 Mar 2016 18:48:14 GMT

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:

http://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

http://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/

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.

Re: Entering Second Observation into Previous Observation Column

laneylaners — Thu, 24 Mar 2016 19:01:52 GMT

Thanks! However, i'm not seeing the links you indicated for lead/accessing the next record.

Re: Entering Second Observation into Previous Observation Column

Reeza — Thu, 24 Mar 2016 19:04:56 GMT

No...apparently the wrong link posted...search does work though 😉

http://communities.sas.com/t5/SAS-Communities-Library/How-to-simulate-the-LEAD-function-opposite-of-LAG/ta-p/232151

Re: Entering Second Observation into Previous Observation Column

Ksharp — Fri, 25 Mar 2016 01:16:58 GMT

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;