topic Re: proc sql with subquery in SAS Procedures

proc sql with subquery

Hoz — Thu, 08 Nov 2012 13:18:29 GMT

Hello,

I would appreciate you would let me know if you know the way.

I'd like to get the following result by one proc sql statement with sql subquery

The statement is

proc sql;

create table titles as

select .....;

quit;

Data is

code title codeA

...

1 aaa 3

2 bbb 3

3 ccc 3

1 aaaa 4

2 bbbb 4

3 cccc 4

.....

There is a desirable result.

title1 title2 title3 code

aaa bbb ccc 3

aaaa bbbb cccc 4

...

Thank you,

Hide

Re: proc sql with subquery

LarryWorley — Thu, 08 Nov 2012 14:11:32 GMT

So I am assuming your requirement is one record per CodeA with the three values of title. This sql code would do that:

proc sql ;

select d1.codeA as code
,d1.title as title1

,d2.title as title2

,d3.title as title3

from is d1

inner join is d2

on d1.codea = d2.codeA

inner join is d3

on d1.codeA = d3.codeA

;

quit ;.

Re: proc sql with subquery

chrismtster — Thu, 08 Nov 2012 14:17:40 GMT

Hide,

There's a few ways of doing this, but here's the simplest way to debug and will work efficiently enough if your dataset isn't too big.

proc sql;

create table t as

select a.title as title1,b.title as title2,c.title as title3,a.code

from data as a

inner join

data as b

on a.codeA = b.codeA

inner join

data as c

on a.codeA = c.codeA

where a.code=1 and b.code=2 and c.code=3

;

quit;

Best,

Chris

Re: proc sql with subquery

LarryWorley — Thu, 08 Nov 2012 14:21:57 GMT

Chris,

Thanks for posting the correct solution. I forgot the where clause in my solution.

Larry

Re: proc sql with subquery

Haikuo — Thu, 08 Nov 2012 14:35:53 GMT

You don't have to use SQL (well, unless it is sort of homework requirement):

Data have;

input (code title codeA) (:$);

cards;

1 aaa 3

2 bbb 3

3 ccc 3

1 aaaa 4

2 bbbb 4

3 cccc 4

;

proc transpose data=have out=want (rename=codea=code drop=_name_) prefix=title;

by codea;

var title;

run;

proc print;run;

Haikuo

Re: proc sql with subquery

LarryWorley — Thu, 08 Nov 2012 14:46:31 GMT

Nor do you have to use proc transpose unless it is a homework assignment.

Choose what you are most comfortable with and what your standards require. If you are working on large datasets then performance should be a consideration as proc transpose would be more efficient -- fewer passes through data and special purpose memory procedure rather than general purpose procedure.

Re: proc sql with subquery

Hoz — Fri, 09 Nov 2012 02:01:01 GMT

Dear LarryWorley, chriismtster,Hal.kuo,

Thank you for a lot of responses.

proc transpose is good and thank you for giving me goog advice.

Since sdataset is big and code value is flexible, the answer using proc sql doesn't work...

My specific task is to get the mean value of test data each site.

data:

valuecode value sitecode

1 1.11 3

2 2.22 3

1 1.1 4

2 2.2 4

...

result dataset ( and then write to excel)

mean1 mean2 ... mean10

1.11 2.22 2.34

The script I thought:

%do I=1 %TO 10;

proc sql;

create table have&I as

select value&I from alldata where code = I;

data meandata;

merge meandata have&I;

run;

%end;

proc means data=meandata;

var value1-value10;

output=result mean(value1)=mean1 ...;

run;

It works good.But I thought it doesn't good script, so I want to know

how sas masters think.

Hide