topic Re: Merging multiple results in one place? in SAS Procedures

Merging multiple results in one place?

MustafaAbdelhaq — Wed, 27 May 2015 09:26:34 GMT

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.

Re: Merging multiple results in one place?

Steelers_In_DC — Wed, 27 May 2015 12:10:28 GMT

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.

Re: Merging multiple results in one place?

RW9 — Wed, 27 May 2015 12:54:40 GMT

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;

Re: Merging multiple results in one place?

Ksharp — Wed, 27 May 2015 13:11:12 GMT

SQL :

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

from sashelp.class(obs=1);

Xia Keshan

Re: Merging multiple results in one place?

Astounding — Wed, 27 May 2015 13:57:55 GMT

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.