BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
turcay
Lapis Lazuli | Level 10

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;
1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Lapis Lazuli | Level 10

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Lapis Lazuli | Level 10

Thank you the code worked 🙂

MikeZdeb
Rhodochrosite | Level 12

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
Lapis Lazuli | Level 10

Thanks a lot. It is working 🙂

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
  • 6 replies
  • 902 views
  • 1 like
  • 3 in conversation