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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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.

View solution in original post

4 REPLIES 4
Steelers_In_DC
Barite | Level 11

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Ksharp
Super User

SQL :

select (sub-query-1) as a,(sub-query-2) as b , ...........

from sashelp.class(obs=1);

Xia Keshan

Astounding
PROC Star

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.

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!

What is Bayesian Analysis?

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.

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
  • 4 replies
  • 845 views
  • 6 likes
  • 5 in conversation