proc sql with subquery

[Hoz]

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

[LarryWorley]

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 ;.

[chrismtster]

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

[LarryWorley]

Chris,

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

Larry

[Haikuo]

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

[LarryWorley]

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.

[Hoz]

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