topic Re: How to simplely transpose one row into one column? in SAS Procedures

How to simplely transpose one row into one column?

bbb_NG — Fri, 13 Apr 2012 02:38:53 GMT

I run a proc sql in sas to extract a table with only one row but with many variables. and in terminal use ,it is for input into excle as column, which need a manual action of write transpose function in excel. can i do it in sas?

for eg

sas output:

lessthan 20	20-30	30-40	over40
60	80	59	68

execl format

lessthan 20	60
20-30	80
30-40	59
over40	68

Thanks in advance.

Re: How to simplely transpose one row into one column?

shivas — Fri, 13 Apr 2012 03:19:27 GMT

Hi,

Try this..

data have;

input range $ 12. number;

cards;

lessthan-20 60

20-30 80

30-40 59

over40 68

;

run;

proc transpose date=have out=want;

id range;

var number;

run;

proc transpose data=want out=want1;run;

Thanks,

Shiva

Re: How to simplely transpose one row into one column?

bbb_NG — Fri, 13 Apr 2012 03:27:10 GMT

shivas ,

Thanks for giving a solution. In fact it's based on the following code as eg,

I want the following output to be transposed in sas first to be one column, no specific needs for the column's name.

select '3' as cnt,

SUM(CASE WHEN AccountValidP not in ('1','2','3','4') THEN 1 ELSE 0 END) AS _11exist_c_nill,

SUM(CASE WHEN sumliabp =0 and AccountValidP in ('1','2','3','4') THEN 1 ELSE 0 END) AS _12exist_c_0,

SUM(CASE WHEN sumliabp >0 and sumliabp <100000 THEN 1 ELSE 0 END) AS _13exist_c_0_10k,

SUM(CASE WHEN sumliabp >=100000 and sumliabp <299999.99 THEN 1 ELSE 0 END) AS _14exist_c_10k_30k,

SUM(CASE WHEN sumliabp >=299999.99 and sumliabp <1000000 THEN 1 ELSE 0 END) AS _15exist_c_30k_1m,

SUM(CASE WHEN sumliabp >=1000000 and sumliabp <5000000 THEN 1 ELSE 0 END) AS _16exist_c_1m_5m,

SUM(CASE WHEN sumliabp >=5000000 THEN 1 ELSE 0 END) AS _17exist_c_gt5m,

SUM(CASE WHEN AccountValidP not in ('1','2','3','4') then sum(sumliab,-sumliabp) ELSE 0 END)/1000 AS _181exist_b_nill,

SUM(CASE WHEN sumliabp =0 and AccountValidP in ('1','2','3','4') then sum(sumliab,-sumliabp) ELSE 0 END)/1000 AS _182exist_b_0,

SUM(CASE WHEN sumliabp >0 and sumliabp <100000 then sum(sumliab,-sumliabp) ELSE 0 END)/1000 AS _183exist_b_0_10k,

SUM(CASE WHEN sumliabp >=100000 and sumliabp <299999.99 then sum(sumliab,-sumliabp) ELSE 0 END)/1000 AS _184exist_b_10k_30k,

SUM(CASE WHEN sumliabp >=299999.99 and sumliabp <1000000 then sum(sumliab,-sumliabp) ELSE 0 END)/1000 AS _185exist_b_30k_1m,

SUM(CASE WHEN sumliabp >=1000000 and sumliabp <5000000 then sum(sumliab,-sumliabp) ELSE 0 END)/1000 AS _186exist_b_1m_5m,

SUM(CASE WHEN sumliabp >=5000000 then sum(sumliab,-sumliabp) ELSE 0 END)/1000 AS _187exist_b_gt5m

FROM WU1.CUSTBASE2M AS t1

WHERE t1.'Ver Date'n = &curdate AND t1.'New Cust Ind'n ne 'Y' and sum(sumliab,-sumliabp)>0

Re: How to simplely transpose one row into one column?

Linlin — Fri, 13 Apr 2012 03:34:32 GMT

try this:

data have;

input range $ :12. number;

cards;

lessthan-20 60

20-30 80

30-40 59

over40 68

;

run;

proc transpose data=have out=want(drop=_name_);

var number;

id range;

run;

proc print;run;

lessthan_

Obs 20 _20N30 _30N40 over40

1 60 80 59 68

(you need to rename your variables)

Linlin

Re: How to simplely transpose one row into one column?

bbb_NG — Fri, 13 Apr 2012 03:39:20 GMT

Linlin ,3q.

in fact i HAVE data=want, and WANT data=have,

transformed from want to have

have this

1	2	3	4	5	6
15	568	9	8	8	9

want this

no_name

568

just by order

Re: How to simplely transpose one row into one column?

shivas — Fri, 13 Apr 2012 04:02:39 GMT

Hi,

Try this..if your data is in this format(have data set) or try second transpose if your data is in want format.

data have;

input a1-a6;

cards;

15 568 9 8 8 9

;

run;

proc transpose date=have out=want(drop=_NAME_);

run;

proc transpose data=want out=want1;run;

Thanks,

Shiva

Re: How to simplely transpose one row into one column?

bbb_NG — Fri, 13 Apr 2012 06:50:29 GMT

shivas,

Thanks. it works. drop=_name_ works. thanks.