Proc Transpose Question

Accepted Solution Solved
Reply
Contributor
Posts: 62
Accepted Solution

Proc Transpose Question

Hi SAS friends,

Can you help me flip my data?

data have;

  input cbg $12. date $5. TOTAL_ADDR SDU MDU 8.;

  cards;

010010201001Q1'14 251 237 14

010010201001Q3'13 252 238 14

010010201001Q4'13 251 237 14

010010201002Q1'14 454 449 5

010010201002Q3'13 450 445 5

010010201002Q4'13 454 449 5

010010202001Q1'14 348 346 2

010010202001Q3'13 370 368 2

010010202001Q4'13 348 346 2

run;

Want:

CBGTOTAL_ADDR_Q1'14SDU_Q1'14MDU_Q1'14TOTAL_ADDR_Q3'13SDU_Q3'13MDU_Q3'13TOTAL_ADDR_Q4'13SDU_Q4'13MDU_Q4'13
10010201001251237142522381425123714
10010201002454449545044554544495
10010202001348346237036823483462

Accepted Solutions
Solution
‎04-10-2014 04:11 PM
Respected Advisor
Posts: 3,799

Re: Proc Transpose Question

Flip and Flop.  Also that 8. at the end of your input statement doesn't do what you think.

data have;
   input cbg $12. date $5. TOTAL_ADDR SDU MDU;
   cards;
010010201001Q1'14 251 237 14
010010201001Q3'13 252 238 14
010010201001Q4'13 251 237 14
010010201002Q1'14 454 449 5
010010201002Q3'13 450 445 5
010010201002Q4'13 454 449 5
010010202001Q1'14 348 346 2
010010202001Q3'13 370 368 2
010010202001Q4'13 348 346 2
;;;;
   run;
proc transpose data=have out=t1;
   by cbg date;
   run;
proc transpose data=t1 out=t2(drop=_name_) delimiter=_;
   by cbg;
   id _name_ date;
   var col1;
   run;
proc print;
  
run;

View solution in original post


All Replies
Solution
‎04-10-2014 04:11 PM
Respected Advisor
Posts: 3,799

Re: Proc Transpose Question

Flip and Flop.  Also that 8. at the end of your input statement doesn't do what you think.

data have;
   input cbg $12. date $5. TOTAL_ADDR SDU MDU;
   cards;
010010201001Q1'14 251 237 14
010010201001Q3'13 252 238 14
010010201001Q4'13 251 237 14
010010201002Q1'14 454 449 5
010010201002Q3'13 450 445 5
010010201002Q4'13 454 449 5
010010202001Q1'14 348 346 2
010010202001Q3'13 370 368 2
010010202001Q4'13 348 346 2
;;;;
   run;
proc transpose data=have out=t1;
   by cbg date;
   run;
proc transpose data=t1 out=t2(drop=_name_) delimiter=_;
   by cbg;
   id _name_ date;
   var col1;
   run;
proc print;
  
run;
Super User
Posts: 10,041

Re: Proc Transpose Question

Transpose dataset by MERGE .

data have;
   input cbg $12. date $5. TOTAL_ADDR SDU MDU;
   cards; 
010010201001Q1'14 251 237 14
010010201001Q3'13 252 238 14
010010201001Q4'13 251 237 14
010010201002Q1'14 454 449 5
010010201002Q3'13 450 445 5
010010201002Q4'13 454 449 5
010010202001Q1'14 348 346 2
010010202001Q3'13 370 368 2
010010202001Q4'13 348 346 2
;;;;
   run; 
proc sql;
 select distinct 'have(where=(date="'||date||'") rename=(total_addr=total_addr_'||translate(date,'_',"'")||' sdu=sdu_'||translate(date,'_',"'")||' mdu=mdu_'||translate(date,'_',"'")||'))'
  into : list separated by ' '
  from have;
quit;

data want;
 merge &list ;
 by cbg;
 drop date;
run;

Xia Keshan

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 216 views
  • 3 likes
  • 3 in conversation