DATA Step, Macro, Functions and more

Entering Second Observation into Previous Observation Column

Reply
Contributor
Posts: 39

Entering Second Observation into Previous Observation Column

I have the following data set:

dataset.JPG

 

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:

 

dataset2.JPG

 

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;

 

Super User
Posts: 19,817

Re: Entering Second Observation into Previous Observation Column

Posted in reply to laneylaners

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...

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. 

 

 

Contributor
Posts: 39

Re: Entering Second Observation into Previous Observation Column

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

Super User
Posts: 19,817

Re: Entering Second Observation into Previous Observation Column

Posted in reply to laneylaners

No...apparently the wrong link posted...search does work though Smiley Wink

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

 

 

delete search sample.JPG

Super User
Posts: 10,035

Re: Entering Second Observation into Previous Observation Column

Posted in reply to laneylaners
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;

Ask a Question
Discussion stats
  • 4 replies
  • 203 views
  • 0 likes
  • 3 in conversation