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
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 ;.
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
Chris,
Thanks for posting the correct solution. I forgot the where clause in my solution.
Larry
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
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.
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
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: