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: 7,439

Re: How to transpose data in Proc Sql

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: 386

Re: How to transpose data in Proc Sql

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: 7,736

Re: How to transpose data in Proc Sql

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

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: 386

Re: How to transpose data in Proc Sql

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
  • 344 views
  • 3 likes
  • 4 in conversation