Hi all,
I am trying to use the proc transpose.
PROC TRANSPOSE DATA=WORK.TRANS_3DC_CD_VALUES_SORT OUT=WORK.TRANS_3DC_CD_VALUES PREFIX=Column NAME=Source LABEL=Label; BY A; VAR B C D E F G H; RUN; QUIT;
It's quite easy when you know the number of column like here A to H
But I need to transpose by A all following columns, without knowing the number and the names of columns.
I want to do something like this but this is not working:
PROC TRANSPOSE DATA=WORK.TRANS_3DC_CD_VALUES_SORT OUT=WORK.TRANS_3DC_CD_VALUES PREFIX=Column NAME=Source LABEL=Label; BY A; VAR ALL except A; RUN; QUIT;
How can I do it?
Thanks
You could use the metadata to pull all the variables other "A" into a macro variable and use that in your proc transpose. Tweak the below as needed for your purposes. Be careful of variable types and case sensitivity.
proc sql;
SELECT name INTO :transposeMe SEPARATED BY ' '
FROM sashelp.vcolumn
WHERE libname = 'WORK' AND
memname = 'TRANS_3DC_CD_VALUES_SORT' AND
type = 'num' /* <--- My assumption. */
name ne 'A';
quit;
PROC TRANSPOSE DATA=WORK.TRANS_3DC_CD_VALUES_SORT
OUT=WORK.TRANS_3DC_CD_VALUES
PREFIX=Column
NAME=Source
LABEL=Label;
BY A;
VAR &transposeMe;
RUN; QUIT;
You could use the metadata to pull all the variables other "A" into a macro variable and use that in your proc transpose. Tweak the below as needed for your purposes. Be careful of variable types and case sensitivity.
proc sql;
SELECT name INTO :transposeMe SEPARATED BY ' '
FROM sashelp.vcolumn
WHERE libname = 'WORK' AND
memname = 'TRANS_3DC_CD_VALUES_SORT' AND
type = 'num' /* <--- My assumption. */
name ne 'A';
quit;
PROC TRANSPOSE DATA=WORK.TRANS_3DC_CD_VALUES_SORT
OUT=WORK.TRANS_3DC_CD_VALUES
PREFIX=Column
NAME=Source
LABEL=Label;
BY A;
VAR &transposeMe;
RUN; QUIT;
Thanks for this!
HAVE
====
Up to 40 obs WORK.CLSSRT total obs=11
Obs SEX AGE HEIGHT WEIGHT
1 F 11 51.3 50.5
2 M 11 57.5 85.0
3 F 12 59.8 84.5
4 M 12 57.3 83.0
5 F 13 56.5 84.0
6 M 13 62.5 84.0
7 F 14 62.8 102.5
8 M 14 69.0 112.5
9 F 15 62.5 112.5
10 M 15 67.0 133.0
11 M 16 72.0 150.0
WANT (Without variable 'AGE')
==============================================
Up to 40 obs WORK.CLSXPO total obs=12
Obs AGE _NAME_ F M
1 11 HEIGHT 51.3 57.5
2 11 WEIGHT 50.5 85
3 12 HEIGHT 59.8 57.3
4 12 WEIGHT 84.5 83
5 13 HEIGHT 56.5 62.5
6 13 WEIGHT 84 84
7 14 HEIGHT 62.8 69
8 14 WEIGHT 102.5 112.5
9 15 HEIGHT 62.5 67
10 15 WEIGHT 112.5 133
11 16 HEIGHT 72
12 16 WEIGHT 150
SOLUTION
proc sort data=sashelp.class(drop=name) out=clssrt nodupkey;;
by age sex;
run;quit;
proc transpose data=clssrt out=clsxpo(where=(_name_ not in ("AGE","SEX"))) ;
by age;
var _all_;
id sex;
run;quit;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.