DATA Step, Macro, Functions and more

Matching Value Issue

Accepted Solution Solved
Reply
Super Contributor
Posts: 381
Accepted Solution

Matching Value Issue

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;

Accepted Solutions
Solution
‎09-29-2015 06:41 PM
Super User
Super User
Posts: 7,407

Re: Matching Value Issue

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.

View solution in original post


All Replies
Super User
Super User
Posts: 7,407

Re: Matching Value Issue

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.

Super Contributor
Posts: 381

Re: Matching Value Issue

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 Smiley Happy

 

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;
Solution
‎09-29-2015 06:41 PM
Super User
Super User
Posts: 7,407

Re: Matching Value Issue

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.

Super Contributor
Posts: 381

Re: Matching Value Issue

Thank you the code worked Smiley Happy

Valued Guide
Posts: 765

Re: Matching Value Issue

[ Edited ]

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

 

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
;

Super Contributor
Posts: 381

Re: Matching Value Issue

Thanks a lot. It is working Smiley Happy

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 237 views
  • 1 like
  • 3 in conversation