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

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
1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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

2 REPLIES 2
data_null__
Jade | Level 19

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
Super User

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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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