Dataset tanspose using Proc SQL

Reply
Occasional Contributor
Posts: 17

Dataset tanspose using Proc SQL

Hi All,

Can anyone tell me whether it is possible to transpose rows into columns using Proc SQL in SAS? 

Thank you.

Super Contributor
Posts: 578

Re: Dataset tanspose using Proc SQL

It is...sometimes....but why would you want to?

db

Occasional Contributor
Posts: 17

Re: Dataset tanspose using Proc SQL

Just for a knowledge, I read about the PIVOT in SQL so want to know whether is it possible in SAS Proc SQL too......

Super User
Posts: 19,167

Re: Dataset tanspose using Proc SQL

No, and it seems like you still need to know the number of records so it's not as useful as proc transpose.

FedSQL is available in SAS 9.4 which is the implantation of ANSI 1999 SQL>

Or if you're working off a database you can use SQL explicit pass through to use SQL that is native to the database.

Super Contributor
Posts: 578

Re: Dataset tanspose using Proc SQL

If you absolutely had to do it, and if your data include enough keys:

Assuming your data looks like:

pk primary key,

colname,

colvalue

proc sql;

create table want as

select

     pk,

    count(*) as records,

     min(case when colname = 'xxx' then colvalue end) as col1,

     min(case when colname='yyy' then colvalue end) as col2,

...

from have

group by pk

Super User
Super User
Posts: 7,720

Re: Dataset tanspose using Proc SQL

As has been mentioned above a few times, why do you need to do this?  Its really not in the principal of SQL which is built to work with normalised (vertical) and relational (i.e. not all in one table) data.  SAS can be thought of as having components for both normalised (i.e. by group) or transposed (i.e. array), but not as straight forward on the relational.  So what is the purpose behind this, use the right tool for the right task.  If you want pure SQL in your database, then Google SQL transpose <database> and you will find half a million results for discussions on how to do this, and some databases have specific functionality to help with this - probably where you have got pivot from as that looks to be T-sql or SQL server.

Super User
Posts: 5,388

Re: Dataset tanspose using Proc SQL

Like @dbailey says, it's possible. But SQL is not optimized for this kind of operations, at least ANSI SQL.

It would involve self joins and group by by (to compress transposed rows).

Use PROC TRANSPOSE instead.

Data never sleeps
Ask a Question
Discussion stats
  • 6 replies
  • 460 views
  • 1 like
  • 5 in conversation