02-24-2015 11:43 AM
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.
03-02-2015 09:10 AM
If you absolutely had to do it, and if your data include enough keys:
Assuming your data looks like:
pk primary key,
create table want as
count(*) as records,
min(case when colname = 'xxx' then colvalue end) as col1,
min(case when colname='yyy' then colvalue end) as col2,
group by pk
03-02-2015 09:19 AM
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.
02-23-2015 09:54 AM
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.