BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
user_
Calcite | Level 5

 


I have the following dataset:

 

 

data have;
 format id1 id2 $30. value 8.;
 input id1 id2 value;
 datalines;
a	b	10
a	c	2
a	d	6
b	a	5
c	a	3
c	d	7
;
run;

 

and I would like to get the following one:

 

 

data want;
 format id1 id2 $30. value 8.;
 input id1 id2 value;
 datalines;
a	b	5
a	d	6
c	a	1
c	d	7
;
run;


In practice I have
- looked for non ordered couples across the first two rows,
- kept the couple such that the value is bigger,
- and assigned it the difference between values.

 

Any help would be very much appreciated.

 

Many thanks!!

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

So I slightly changed the code:

data inter;
set have;
length key $60;
if id1 > id2
then key = id2 !! id1;
else key = id1 !! id2;
run;

proc sort data=inter;
by key value;
run;

data want (keep=id1 id2 value);
set inter;
by key;
oldval = lag(value);
if first.key and last.key then output;
else if not first.key
then do;
  value = abs(oldval - value);
  output;
end;
drop oldval;
run;

The id1 > id2 is just there to unify the creation of the key variable.

If you somehow need to preserve the original order, we'd have to store _n_ in the data inter; step and sort the end result by that.

View solution in original post

3 REPLIES 3
Kurt_Bremser
Super User
data have;
 format id1 id2 $30. value 8.;
 input id1 id2 value;
 datalines;
a b 10
a c 2
a d 6
b a 5
c a 3
c d 7
;
run;

data inter;
set have;
if id1 > id2
then do;
  x = id1;
  id1 = id2;
  id2 = x;
end;
drop x;
run;

proc sort data=inter;
by id1 id2 descending value;
run;

data want;
set inter;
by id1 id2;
oldval = lag(value);
if first.id2 and last.id2 then output;
else if not first.id2
then do;
  value = oldval - value;
  output;
end;
drop oldval;
run;

creates the values, but with a slightly different order of observations and ID's within the observations.

user_
Calcite | Level 5

Thank you for your reply.

 

the order of observations is not important, but the order of IDs is fundamental.

The pairs that appears in the final dataset needs to be the one with higher value, that is the difference between the values of the two couples having the same elements has to be positive. In the dataset Want that you suggested the value assigned to the pair (a c) should be -1 rather than 1.

 

Furthermore, I cannot not rely on "id1 > id2" as the id1 and id2 could be any string ($30).

Kurt_Bremser
Super User

So I slightly changed the code:

data inter;
set have;
length key $60;
if id1 > id2
then key = id2 !! id1;
else key = id1 !! id2;
run;

proc sort data=inter;
by key value;
run;

data want (keep=id1 id2 value);
set inter;
by key;
oldval = lag(value);
if first.key and last.key then output;
else if not first.key
then do;
  value = abs(oldval - value);
  output;
end;
drop oldval;
run;

The id1 > id2 is just there to unify the creation of the key variable.

If you somehow need to preserve the original order, we'd have to store _n_ in the data inter; step and sort the end result by that.

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