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

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 🙂 But I'm not sure how to fix the warning sign... 

 

 


GOT.JPGhave.JPGhave_orcl.JPG
1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

"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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

2 REPLIES 2
mkeintz
PROC Star

"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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
ginak
Quartz | Level 8

YES!! This is correct! and it worked for me with no errors. Thank you so much 🙂 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!!!

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