How to remove prefix number 1 after proc transpose?

[ybz12003]

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;

[Jagadishkatam]

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;

[art297 · Accepted solution]

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

[MikeZdeb]

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

[art297]

@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