BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bbb_NG
Fluorite | Level 6


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 2020-3030-40over40
60805968

execl format

lessthan 2060
20-3080
30-4059
over4068

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
shivas
Pyrite | Level 9

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

View solution in original post

6 REPLIES 6
shivas
Pyrite | Level 9

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

bbb_NG
Fluorite | Level 6

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

Linlin
Lapis Lazuli | Level 10

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

bbb_NG
Fluorite | Level 6

Linlin ,3q.

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

transformed from want to have

have this

123456
15568988

9

want this

no_name
15
568
9
8
8
9

just by order

shivas
Pyrite | Level 9

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;

or

proc transpose data=want out=want1;run;

Thanks,

Shiva

bbb_NG
Fluorite | Level 6

shivas,

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 8275 views
  • 3 likes
  • 3 in conversation