BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
fabdu92
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
collinelliot
Barite | Level 11

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;

View solution in original post

3 REPLIES 3
collinelliot
Barite | Level 11

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;
fabdu92
Obsidian | Level 7

Thanks for this!

rogerjdeangelis
Barite | Level 11
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;

SAS INNOVATE 2024

Innovate_SAS_Blue.png

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. 

Register now!

What is Bayesian Analysis?

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 11567 views
  • 1 like
  • 3 in conversation