How to remove prefix number 1 after proc transpose?

Accepted Solution Solved
Reply
Super Contributor
Posts: 274
Accepted Solution

How to remove prefix number 1 after proc transpose?

Hello:

 

I have the followinng proc transpose program.  A new column name 'bd_dx1' created after transpose, number 1 is automatic assigned after bd_dx.   Is ther remove number 1?  I have twenty very similar prefix names like bd_dx.   Thanks.

proc transpose data=test out=trans

prefix=bd_dx;

var bd_dx_1-bd_dx_7;

by Name;

run;

quit;

 


Accepted Solutions
Solution
3 weeks ago
Esteemed Advisor
Posts: 7,293

Re: How to remove prefix number 1 after proc transpose?

Easiest way may be to create and use a macro variable to specify both the vars to be transposed and the name to be assigned. e.g.:

%let var=bd_dx;
proc transpose data=test out=trans(rename=(col1=&var.));
  var &var.:;
  by Name;
run;

Art, CEO, AnalystFinder.com

View solution in original post


All Replies
Super User
Posts: 1,108

Re: How to remove prefix number 1 after proc transpose?

[ Edited ]

if you dont keep the prefix i believe a variable name col1 is created. Please correct me. If that is the case then rename col1=bd_dx as below.

 

you could also rename the _name_ variable an automatic variable created with proc transpose with bd_dx_1 and so on .......... with name option

proc transpose data=test out=trans(rename=(col1=bd_dx)) name=xxxx ;
var bd_dx_1-bd_dx_7;
by Name;
run;

quit;

Thanks,
Jag
Solution
3 weeks ago
Esteemed Advisor
Posts: 7,293

Re: How to remove prefix number 1 after proc transpose?

Easiest way may be to create and use a macro variable to specify both the vars to be transposed and the name to be assigned. e.g.:

%let var=bd_dx;
proc transpose data=test out=trans(rename=(col1=&var.));
  var &var.:;
  by Name;
run;

Art, CEO, AnalystFinder.com

Valued Guide
Posts: 763

Re: How to remove prefix number 1 after proc transpose?

[ Edited ]

Hi, same as Art's ... seems like just as much effort without the macro variable ...

 

data x;
input name :$5. bd_dx_1-bd_dx_7;
datalines;
bob 1 2 3 4 5 6 7
carol 9 9 9 9 9 9 9
ted 0 0 0 0 0 0 0
alice 7 6 5 4 3 2 1
;

 

proc transpose data=x out=trans (drop=_name_ rename=(col1=bd_dx));
var bd_dx: ;
by name notsorted;
run;

 

PORTION OF DATA SET: trans

name bd_dx

bob   1
bob   2
bob   3
bob   4
bob   5
bob   6
bob   7
carol 9
carol 9

 

Esteemed Advisor
Posts: 7,293

Re: How to remove prefix number 1 after proc transpose?

@MikeZdeb, I proposed the macro variable since @ybz12003 indicated that there were 20 some sets of variables that had to be transposed. It was never mentioned whether they were all from the same file, or different files, so I didn't write the macro wrapper or call execute code but, rather, just suggested the macro variable as a proof of concept.

For 20 sets of variables, I would cut the chance of typo errors down by at least half.

 

Art, CEO, AnalystFinder.com

 

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 151 views
  • 3 likes
  • 4 in conversation