BookmarkSubscribeRSS Feed
hansavvy
Calcite | Level 5

hi all,

onthis for a good hour cant find a sol in any threads. 

 

Code......

proc transpose data=t_class out=t2_class(drop=_:);
by age;
/* 2 variables into 1 columnname */
id sex name;
var weight;
run;

Current output.....

hansavvy_0-1703033164444.png

 

Desired ouput....

need suffix or prefix /delimiter separating 'sex' & 'name'

 

5 REPLIES 5
mkeintz
PROC Star

I don't think proc transpose offers that possibility on ID variables.  You can create a variable concatenating SEX and NAME prior to the transpose:

 

data vtemp /view=vtemp;
  set t_class;
  sex_name=catx('_',sex,name);
run;

proc transpose data=vtemp out=tw_class(drop=_:);
  by age;
  /* 2 variables into 1 columnname */
  id sex_name;
  var weight;
run;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
hansavvy
Calcite | Level 5

thnx but i found this gem

 

delimiter=_ ;

proc transpose data=t_class out=t2_class(drop=_:) delimiter=_ ;
by age;
/* 2 variables into 1 columnname */
id sex name;
var weight;
run;

output 

hansavvy_0-1703034704898.png

 

it does the job, but couldn't add more characters otherthan '_' guess limitation with delimiter only 1 allowed

thx anyways buddy

Patrick
Opal | Level 21

Using more than one character works for me.

proc transpose data=t_class out=t2_class(drop=_:) delimiter=_X_;
  by age;
  /*  2 variables into 1 columnname */
  id sex name;
  var weight;
run;

Patrick_0-1703035960874.png

 

ballardw
Super User

@hansavvy wrote:

thnx but i found this gem

 

delimiter=_ ;

proc transpose data=t_class out=t2_class(drop=_:) delimiter=_ ;
by age;
/* 2 variables into 1 columnname */
id sex name;
var weight;
run;

output 

hansavvy_0-1703034704898.png

 

it does the job, but couldn't add more characters otherthan '_' guess limitation with delimiter only 1 allowed

thx anyways buddy


If you think that you want other characters in the name of the variables, which is one interpretation of your original "need suffix or prefix /delimiter separating 'sex' & 'name'" not explicitly showing you want M/Thomas then you are running into the basic limits of SAS variable names of letter, digit or _ character by default. The steps to allow use of other characters means that you have to change system settings for VALIDVARNAME to allow other characters and then every single one that uses the non-standard naming needs to have the name enclosed in quotes followed by an N to indicate it is a SAS name literal: "M/Thomas"n  for example. Most find that gets very old very quickly and with the potential number of variables created by Transpose the mind boggles on how much extra typing this could impose.

 

How exactly do you expect to use this resulting data set? Such wide sets often become cumbersome to work with very quickly.

PaigeMiller
Diamond | Level 26

@ballardw wrote:


If you think that you want other characters in the name of the variables, which is one interpretation of your original "need suffix or prefix /delimiter separating 'sex' & 'name'" not explicitly showing you want M/Thomas then you are running into the basic limits of SAS variable names of letter, digit or _ character by default. The steps to allow use of other characters means that you have to change system settings for VALIDVARNAME to allow other characters and then every single one that uses the non-standard naming needs to have the name enclosed in quotes followed by an N to indicate it is a SAS name literal: "M/Thomas"n  for example. Most find that gets very old very quickly and with the potential number of variables created by Transpose the mind boggles on how much extra typing this could impose.

 

How exactly do you expect to use this resulting data set? Such wide sets often become cumbersome to work with very quickly.


Agreeing with @ballardw — if this is an exercise to learn something about PROC TRANSPOSE, I guess its okay, but if you are planning to do this on real data (not this CLASS data set) for a real project, I would advise you to re-think the process forward, because as stated "such wide (data) sets often become cumbersome to work with very quickly". Most SAS procedures work very well on long data sets; few (if any) procedures work well on wide data sets. Even if you are doing a report where you want a wide report with (for example, City as the vertical variable in the report and Year/Week as the horizontal variable), this is perfectly reasonable to do; but you do this with a long SAS data set and NOT a wide SAS data set via PROC REPORT. SAS has done the hard work to handle this data from the long arrangement, so you don't have to create a wide data set and then have to develop cumbersome code.

--
Paige Miller

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 908 views
  • 3 likes
  • 5 in conversation