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...
... View more
