Hello,
I have multiple queries running from different tables, all of these queries return only one value each. So I want to merge the final values of each query in one place, what is the best way to this.
I've tried to join tables, but in this case extra rows are added.
All I need is to see the final results of each query in single place on the same row.
Thank you for your help.
You might find that staying with SQL is easiest. But there is another way. If you have many one-observation data sets, you could combine them side by side in either of these two ways:
data combined;
merge result1 result2 result3 result4;
run;
Or,
data combined;
set result1;
set result2;
set result3;
set result4;
run;
Good luck.
I think what you are saying is you have several variable that you want on a single row, but as they stand they are unrelated. The easiest way I can think of is to make them be related. Here's an example of giving them a dummy ID to merge on:
data have1;
infile cards dsd;
input one $;
cards;
one
;
run;
data have2;
infile cards dsd;
input two $;
cards;
two
;
run;
data prep1;
set have1;
dummy = 1000;
run;
data prep2;
set have2;
dummy = 1000;
run;
data want(drop=dummy);
merge prep1
prep2;
by dummy;
run;
What I would do, during the queries that you mentioned, set up a dummy variable that will relate each item you want, you can delete this in your final step.
You mention queries, do you mean SQL? If so then:
proc sql;
create table WANT as
select A.RESULT1,
B.RESULT2,
C.RESULT3
from HAVE1 A,
HAVE2 B,
HAVE3 C
on 1=1;
quit;
Although its highly recommended to have some sort of identifier linking the results as Mark Johnson has stated.
proc sql;
create table WANT (drop=dummy) as
select COALESCE(A.DUMMY,B.DUMMY) as DUMMY, /* You will get a note if you omit this */
A.RESULT1,
B.RESULT2
from (select 1 as DUMMY,RESULT1 from HAVE1) A
full join (select 1 as DUMMY,RESULT2 from HAVE2) B
on A.DUMMY=B.DUMMY;
quit;
SQL :
select (sub-query-1) as a,(sub-query-2) as b , ...........
from sashelp.class(obs=1);
Xia Keshan
You might find that staying with SQL is easiest. But there is another way. If you have many one-observation data sets, you could combine them side by side in either of these two ways:
data combined;
merge result1 result2 result3 result4;
run;
Or,
data combined;
set result1;
set result2;
set result3;
set result4;
run;
Good luck.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.