How to transpose data in Proc Sql

Reply
Contributor
Posts: 32

How to transpose data in Proc Sql

Hi,

Is It Possible to transpose data in Proc Sql

PROC Star
Posts: 8,167

Re: How to transpose data in Proc Sql

Posted in reply to SushilKumar

Depends on your definition of transpose. The following, for example, will rotate the table created by the data step below:

data qtrsales;

  informat amount comma12.;

  input Year     Quarter     Amount;

  cards;

   1995     1           125,000.90

   1995     2           136,000.75

   1995     3           212,000.34

   1995     4           328,000.82

   1996     3           728,000.35

   1996     2           422,000.13

   1996     1           328,000.82

;

proc sql;

  create table transposed as

    SELECT distinct YEAR,

       coalesce((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 1 AND YEAR =

  Q.YEAR),0) as q1,

      coalesce((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 2 AND YEAR =

  Q.YEAR),0) as q2,

       coalesce((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 3 AND YEAR =

  Q.YEAR),0) as q3,

       coalesce((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 4 AND YEAR =

  Q.YEAR),0) as q4

     FROM QTRSALES Q

     GROUP BY YEAR

  ;

quit;

Super Contributor
Posts: 398

Re: How to transpose data in Proc Sql

Posted in reply to SushilKumar

Can you expand why you'd want to?  You've got SAS, with many tools for many tasks.  Is there a reason why PROC TRANSPOSE won't transpose your data?

Super User
Super User
Posts: 9,599

Re: How to transpose data in Proc Sql

Posted in reply to SushilKumar

Yes, but its not straight-forward.  Why do you need to do this in SQL, I note that in your other question you also want an SQL solution where a datastep may be more effective.

Contributor
Posts: 32

Re: How to transpose data in Proc Sql

Posted in reply to SushilKumar

Yes we can do tranprose in Proc transpose but i just want to know whether it can be done in Proc Sql.

Thanks Arthur Tabachneck for your response

Super Contributor
Posts: 398

Re: How to transpose data in Proc Sql

Posted in reply to SushilKumar

In your (expensive) kitbag you have a chainsaw and a pocketknife.  If you WANT to chop down that tree with your pocketknife, knock yourself out Smiley Wink

You might have to read the owners manual for the chainsaw, but you'll be a lot more productive once you use it.

Ask a Question
Discussion stats
  • 5 replies
  • 416 views
  • 3 likes
  • 4 in conversation