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

Hello,

I want to compare negative line by positive line in terms of price. the comparison is made on the variables (name, city, age, product). I want a table which contains just the negatives with their positive correspondents and the name of the variable which is different between the two rows, if I find more than one difference between two rows I do not put it in this table like the case of " Oliver "I have two differences in the city and the age.

data have;
input row name $ city $ age product $ price;
CARDS;
1 Philip Paris 43 P1 -10
2 Mikael Marseille 23 P2 -20
3 Oliver Metz 32 P2 -25
4 Lisa Toulouse 43 P1 -15
5 Philip Paris 55 P1 10
6 Michael Marseille 23 P2 20
7 Oliver Lille 23 P1 25
8 Lisa Toulouse 43 P2 15
;
run;

data want;
input negative_row name $ city $ age product $ price positive_row column_diff $;
CARDS;
1 Philip Paris 43 P1 -10 5 age
2 Mikael Marseille 23 P2 -20 6 name
4 Lisa Toulouse 43 P1 -15 8 product
;
run;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

You are right, I'll edit the code.

View solution in original post

5 REPLIES 5
Shmuel
Garnet | Level 18

Try next tested code:

data have;
input  row name $ city $ age product $ price;
CARDS;
1 Philip Paris 43 P1 -10
2 Mikael Marseille 23 P2 -20
3 Oliver Metz 32 P2 -25
4 Lisa Toulouse 43 P1 -15
5 Philip Paris 55 P1 10
6 Michael Marseille 23 P2 20
7 Oliver Lille 23 P1 25
8 Lisa Toulouse 43 P2 15
;
run;

proc sql;
  create table temp as select 
  n.*, 
  p.row as positive_row,
  p.city as p_city,
  p.name as p_name,
  p.product as p_product,
  p.age as p_age,
  (p.price + n.price) as diff
  from have(where=(price < 0)) as n 
  full join have(where=(price>0)) as p
  on n.price = -p.price
  order by row;
quit;
data want;
 set temp(rename=(row=negative_row_));
     length column_diff $6;
     /* d# is either true=1 or false=0 */
     d1 = (name=p_name);
     d2 = (city=p_city);
     d3 = (age=P_age);
     d4 = (product=p_product);
     d5 = (diff=0);
     if sum(of d1-d5) = 4    /* 4 out of 5 variables matches excluding row */
        then do;
           if not d1 then  column_diff = "name"; else
           if not d2 then  column_diff = "city"; else
           if not d3 then  column_diff = "age"; else
           if not d4 then  column_diff = "product"; else
           if not d5 then  column_diff = "price"; 
           output;
        end;
     keep negative_row_ name city age product price positive_row column_diff;
run;
           
           
mazouz
Calcite | Level 5
it's working when I change this: I add not
if not d1 then column_diff = "name"; else
if not d2 then column_diff = "city"; else
if not d3 then column_diff = "age"; else
if not d4 then column_diff = "product"; else
if not d5 then column_diff = "price";
Shmuel
Garnet | Level 18

You are right, I'll edit the code.

Shmuel
Garnet | Level 18

last part of the code:

        then do;
           if not d1 then  column_diff = "name"; else
           if not d2 then  column_diff = "city"; else
           if not d3 then  column_diff = "age"; else
           if not d4 then  column_diff = "product"; else
           if not d5 then  column_diff = "price"; 
           output;
        end;

can be shortened and replaced with next code:

     array nx {5} $ ("name" "city" "age" "product" "price");
     if sum(of d1-d5) = 4    /* 4 out of 5 variables matches excluding row */
        then do; 
column_diff = nx(whichn(0,of d1-d5));
output;
end;

thus making it more dynamic to adapt to greater number of variables.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 5 replies
  • 565 views
  • 1 like
  • 2 in conversation