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;
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
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;
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
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
@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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.