Merging on 2 variables by formatted order using SQL

Accepted Solution Solved
Reply
Contributor
Posts: 67
Accepted Solution

Merging on 2 variables by formatted order using SQL

[ Edited ]

Hello,

 

I have two datasets I'd like to merge. The first one contains odds ratios and confidence intervals, the second contains the corresponding p-values. So each variable has a class statment. E.g., Race6 has 5 levels (asian vs. white, american indian vs. white, etc.). There is also an intercept that I'd like at the top. The problem is, if I sort the data by effect and then classvalue, that it'll put the variables in alphabetical order. But I want them to be sorted by formatted order. So I try to sort them w/ proc sql then merge but it doesn't work (see below), so then I try to merge with a left join (very bottom).

 

in my have.jpg, you can see the formatted names of 'variable' are in the 'effectz' column. So when SAS sorts, it sorts variable in alphabetical order. I'd like to sort it to have it in effectz's order (keep in mind that effectz = variable, except I put the format on effectz just to show you).  

 

Basically, I want the order of have.jpg, but to add on the corresponding odds ratios from the have_orcl.jpg merged in. How can I do this? I tried it two different ways.

 

 

 

/*This is the have.jpg table. I formatted effect to put it in the order I want, 
and then tried to sort it by the order of the effectf format and then by classval0*/ proc sql; create table table3c as select variable as Effect FORMAT = $effectf., classval0, probtb, run from table3b order by put(effect, $effectf.), Classval0; quit;

 

 

When I do this, I get the following message in the log: "NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause." What does this mean? Why is it doing this? It seems to have worked. But then again, I wouldn't know because it's already in order. 

 

 

 

 /*Now sort the have_orcl.jpg table.*/ 
proc sql; create table table4c as select effect2 as Effect FORMAT = $effectf., classval0, orcl, run from table4b order by put(Effect, $effectf.), classval0 ; quit;

When I do this, I get the following message in the log: 

"NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause."

 

 

Now, I want to merge them:

 

data comb2 ;
	merge table3c table4c;
	by Effect classval0;
run;

 

 

And I get this error: 

ERROR: BY variables are not properly sorted on data set WORK.TABLE3C.
Effect=01 Race classval0=6 Other/Multi. probtb=0.8000 Run=1 orcl=1.07 (0.65 - 1.74) FIRST.Effect=0
LAST.Effect=0 FIRST.classval0=1 LAST.classval0=1 _ERROR_=1 _N_=7
NOTE: The SAS System stopped processing this step because of errors.

 

So my goal was to originally merge, but it didn't work, so I tried sorting by the formatted variables so I could merge and have those in that order. That didn't work. So I tried a SQL join:

 

 

proc sql;
     create table MERGEd as
     select     L.*, R.*
     from table3c  as L
     left join  table4c R
     on L.effect=R.effect
     and L.classval0=R.classval0
	 order by put(effect, $effectf.), classval0;
quit;

 

 

I know I'm not supposed to include the ", R.*"  and I get this error when doing so:

 

WARNING: Column named effect is duplicated in a select expression (or a view). Explicit references to
it will be to the first one.
WARNING: Column named classval0 is duplicated in a select expression (or a view). Explicit references
to it will be to the first one.
NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause.
WARNING: Variable Effect already exists on file WORK.MERGED.
WARNING: Variable classval0 already exists on file WORK.MERGED.
WARNING: Variable Run already exists on file WORK.MERGED.

 

 

BUT I am not sure how to do it otherwise. If I don't include the ", R.*" then it won't pull in my orcl column (the column with the Odds ratios (Confidence Interval)) from the have_orcl.jpg. But it still works (see GOT.jpg).. I am just not allowed to submit something with an error statement. Help?

 

 

I have tried to include my code as reference, and also searched the message boards. That's how I got the last bit of SQL code Smiley Happy But I'm not sure how to fix the warning sign... 

 

 


GOT.JPGhave.JPGhave_orcl.JPG

Accepted Solutions
Solution
‎07-05-2017 10:37 PM
Valued Guide
Posts: 797

Re: Merging on 2 variables by formatted order using SQL

"order by" apparently ignores formatted values.  So instead of formatting a variable, create another variable as the formatted value of the first variable.  Then sort by that newly created var:

 

proc sql;
  create table table3c as
    select put(variable,$effect.) as effect,
    classval0, 
    probtb, 
    run
  from table3b
  order by effect, Classval0;
quit;

View solution in original post


All Replies
Solution
‎07-05-2017 10:37 PM
Valued Guide
Posts: 797

Re: Merging on 2 variables by formatted order using SQL

"order by" apparently ignores formatted values.  So instead of formatting a variable, create another variable as the formatted value of the first variable.  Then sort by that newly created var:

 

proc sql;
  create table table3c as
    select put(variable,$effect.) as effect,
    classval0, 
    probtb, 
    run
  from table3b
  order by effect, Classval0;
quit;
Contributor
Posts: 67

Re: Merging on 2 variables by formatted order using SQL

YES!! This is correct! and it worked for me with no errors. Thank you so much Smiley Happy After doing this, I just did a simple merge in a data step and it worked without giving me the 'the variables are not sorted in the proper order' error. Thanks!!!

☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 109 views
  • 1 like
  • 2 in conversation