How to: transpose 2 variables into 1 name with suffix /delimiter separating the 2

[hansavvy]

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.....

Desired ouput....

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

? 

[mkeintz]

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;

[hansavvy]

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 

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

thx anyways buddy

[Patrick]

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;

[ballardw]

---

@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 

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]

---

@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.