Proc Transpose Question

[SAShole]

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:

CBG	TOTAL_ADDR_Q1'14	SDU_Q1'14	MDU_Q1'14	TOTAL_ADDR_Q3'13	SDU_Q3'13	MDU_Q3'13	TOTAL_ADDR_Q4'13	SDU_Q4'13	MDU_Q4'13
10010201001	251	237	14	252	238	14	251	237	14
10010201002	454	449	5	450	445	5	454	449	5
10010202001	348	346	2	370	368	2	348	346	2

[data_null__ · Accepted solution]

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;

[Ksharp]

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