BookmarkSubscribeRSS Feed
laneylaners
Obsidian | Level 7

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;

 

4 REPLIES 4
Reeza
Super User

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. 

 

 

laneylaners
Obsidian | Level 7

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

Ksharp
Super User
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;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 939 views
  • 0 likes
  • 3 in conversation