Transpose a table with one row and many columns using SAS SQL

Question

My table has only 1 row and many columns. I need to return only 1 column and many rows. It is possible to do a transpose via SAS SQL?

Before:

column1 column2 column3 column4
   1       2       3       4

After:

column
   1
   2
   3
   4

Not a simple solution. SQL doesn't allow variable list short cuts. You should use proc transpose. If you insist on SQL then you'll need a macro to first determine the number of columns and then generate your union statements dynamically. I don't think it's worth the effort personally. — Reeza, Nov 03 '16 at 20:47

score 0 · Answer 1 · answered Nov 03 '16 at 17:36

0

Since you mention SAS SQL (as opposed to any other SAS proc), this is a solution, though it will be a bit verbose if you have a lot of columns:

PROC SQL;

    SELECT column1 AS column FROM table
    UNION ALL
    SELECT column2 AS column FROM table
    UNION ALL
    SELECT column3 AS column FROM table
    UNION ALL
    SELECT column4 AS column FROM table;

    /* Add for each column */

QUIT;

This simply unions (stacks) each column.

answered Nov 03 '16 at 17:36

Nick

7,103
2
21
43

but i dont know, how many columns contains table... i cant use this, exist some another solution? – Keliimek Nov 03 '16 at 17:41
Why don't you know how many columns there are in your table?? – Nick Nov 03 '16 at 17:42
because this script must be universal for more tables and each table contains a different number of columns – Keliimek Nov 04 '16 at 08:34

score 0 · Accepted Answer · answered Nov 03 '16 at 18:25

0

Why not use PROC TRANSPOSE you don't have to know how many columns or even the names PROC TRANSPOSE will transpose all numeric columns by default.

proc transpose data= out= ;
   run;

answered Nov 03 '16 at 18:25

data _null_

8,534
12
14

1

SAS will convert all to character. You can just use VAR _ALL_; – data _null_ Nov 04 '16 at 13:13

Transpose a table with one row and many columns using SAS SQL

2 Answers2