BookmarkSubscribeRSS Feed
Hoz
Calcite | Level 5 Hoz
Calcite | Level 5

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

6 REPLIES 6
LarryWorley
Fluorite | Level 6

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
Calcite | Level 5

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
Fluorite | Level 6

Chris,

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

Larry

Haikuo
Onyx | Level 15

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
Fluorite | Level 6

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
Calcite | Level 5 Hoz
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1211 views
  • 0 likes
  • 4 in conversation