Help using Base SAS procedures

Merging multiple results in one place?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Merging multiple results in one place?

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.


Accepted Solutions
Solution
‎05-27-2015 09:57 AM
Super User
Posts: 5,081

Re: Merging multiple results in one place?

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


All Replies
Valued Guide
Posts: 858

Re: Merging multiple results in one place?

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.

Super User
Super User
Posts: 7,395

Re: Merging multiple results in one place?

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;

Super User
Posts: 9,671

Re: Merging multiple results in one place?

SQL :

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

from sashelp.class(obs=1);

Xia Keshan

Solution
‎05-27-2015 09:57 AM
Super User
Posts: 5,081

Re: Merging multiple results in one place?

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.

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 251 views
  • 6 likes
  • 5 in conversation