Matching Value Issue

[turcay]

I have to tables below as X and Y. X table has Q columns which passed several conditions.And Y table contains the Q columns that i want to change. If there are same Q columns on both the tables i receive an error. How can i resolve this problem ? For example I receive an error if both of the tables contain Q1

data table;
length ID $ 7 Q1 8 Q2 8 Q3 8 Q4 8 Q5 8 Q6 8;
infile datalines missover dlm=",";
input ID Q1 Q2 Q3 Q4 Q5 Q6 ;
datalines;
RefID1,0.90,0.80,0.00,0.90,0.00,0.70
RefID2,0.100,0.100,0.00,0.70,0.00,60
RefID3,0.40,0.80,0.00,0.90,0.00,0.50
RefID4,0.55,0.80,0.05,0.90,0.00,0.69
RefID5,0.00,0.80,0.60,0.90,0.20,0.90
RefID6,0.96,0.00,0.40,0.90,0.00,0.95
RefID7,0.00,0.80,0.90,0.90,0.00,0.99
RefID8,0.56,0.80,0.55,0.90,0.00,0.93
RefID9,0.99,0.80,0.99,0.90,0.00,0.70
RefID10,0.89,0.88,0.56,0.90,0.00,0.00
;
run;
data x;
input variable $ percentage;
cards;
Q1 0.9
Q2 0.8
Q4 0.2
Q6 0.5
;
run;
 
data y;
input variable $;
cards;
Q1
Q3
;
proc sql;
select cats(variable,'=',variable,'_') into : list separated by ' '
  from x;
quit;
proc datasets library=work nodetails nolist;
modify table;
rename &list ;
quit;
proc sql;
select cats(variable,'=',variable,'_') into : list2 separated by ' '
  from y;
quit;
proc datasets library=work nodetails nolist;
modify table;
rename &list2 ;
quit;

[RW9]

The reason your code is not working is that in the first datasets step you are renaming all the variables in TABLE which appear in X to have an underscore, so after the first datasets TABLE looks like this:

ID Q1_ Q2_ Q3 Q4_ Q5 Q6_;

You next datasets step is trying to find Q1 and Q3 in that TABLE, which don't exist.  Now you could change your second proc sql to try to pick up on this, i.e. if value appears in dataset with _ etc.  however that starts to become quite complicated.  My quesiton is this, why are you doing this, what is it your trying to achieve?   I would change the operation (if I did it this way in the first place) to:

proc sql;
  delete from X
  where VARIABLE not in (select VARIABLE from Y);
quit;

This then means X contains all the variables to run your check or whatever on.  No need to be renaming variables and creating lists etc.

[turcay]

Thank you. This is how I get the results I want. If you have a better idea, I will be happy to apply. Otherwise, I will consider your response as the right way. These are the tasks assigned to me so I am trying to apply 🙂

proc sql;
  delete from X
  where VARIABLE in (select VARIABLE from Y);
quit;

proc sql;
 select cats(variable,'=',variable,'_') into : list separated by ' '
  from x;
quit;
proc datasets library=work nodetails nolist;
 modify table;
 rename &list ;
quit;

proc sql;
 select cats(variable,'=',variable,'_') into : list2 separated by ' '
  from y;
quit;
proc datasets library=work nodetails nolist;
 modify table;
 rename &list2 ;
quit;

[RW9 · Accepted solution]

Ah, so you want to change any variable in X OR Y to have an underscore.  If so, do it in one go:

proc sql;
  select  distinct cats(VARIABLE,'=',VARIABLE,'_')
  into     :LIST separated by ' '
  from    (select VARIABLE from X union all select VARIABLE from Y);
quit;

proc datasets library=work nodetails nolist;
  modify table;
  rename &list.;
quit;

The above puts X and Y together in the subquery, then from that combined data selects a distinct list of variables to rename, which is then used in the rename.

[turcay]

Thank you the code worked 🙂

[MikeZdeb]

Hi, how about reducing the number of steps (UNION purges duplicate rows by default) ...

proc sql noprint;
select cats(variable,'=',variable,'_') into :list separated by ' ' from
(select variable from x
union
select variable from y);
quit;

proc datasets library=work nodetails nolist;
modify table;
rename &list ;

quit;

ps ... modified data step (less code, numeric length defaults to 😎 ...

data table;
infile datalines dsd;
input ID :$7. Q1-Q6;
datalines;
RefID1,0.90,0.80,0.00,0.90,0.00,0.70
RefID2,0.100,0.100,0.00,0.70,0.00,60
RefID3,0.40,0.80,0.00,0.90,0.00,0.50
RefID4,0.55,0.80,0.05,0.90,0.00,0.69
RefID5,0.00,0.80,0.60,0.90,0.20,0.90
RefID6,0.96,0.00,0.40,0.90,0.00,0.95
RefID7,0.00,0.80,0.90,0.90,0.00,0.99
RefID8,0.56,0.80,0.55,0.90,0.00,0.93
RefID9,0.99,0.80,0.99,0.90,0.00,0.70
RefID10,0.89,0.88,0.56,0.90,0.00,0.00
;

[turcay]

Thanks a lot. It is working 🙂