topic PROC Transpose all variables in SAS Procedures

PROC Transpose all variables

fabdu92 — Tue, 21 Feb 2017 17:19:21 GMT

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

Re: PROC Transpose all variables

collinelliot — Tue, 21 Feb 2017 17:36:40 GMT

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;

Re: PROC Transpose all variables

fabdu92 — Tue, 21 Feb 2017 17:54:13 GMT

Thanks for this!

Re: PROC Transpose all variables

rogerjdeangelis — Tue, 21 Feb 2017 20:44:24 GMT

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;