topic How to remove prefix number 1 after proc transpose? in SAS Programming

How to remove prefix number 1 after proc transpose?

ybz12003 — Sun, 02 Jul 2017 15:24:12 GMT

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;

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

Jagadishkatam — Sun, 02 Jul 2017 15:32:31 GMT

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;

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

art297 — Sun, 02 Jul 2017 15:55:04 GMT

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

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

MikeZdeb — Sun, 02 Jul 2017 17:19:39 GMT

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

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

art297 — Sun, 02 Jul 2017 17:26:30 GMT

@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