topic Re: How do I transpose a data set that has multiple values for an ID variable? in SAS Programming

How do I transpose a data set that has multiple values for an ID variable?

Hello_there — Tue, 23 Aug 2022 14:26:12 GMT

I have this data set that I want to transform so that it's one row per ID. The problem is that there are multiple values for the an ID variable.

Here is the data set in question:

data have1;
infile datalines dsd dlm=",";
	input id $ category $ val $;
datalines;
001, color, blue
001, color, red
002, color, orange
003, color, red
003, color, orange,
003, color, blue
004, color, aqua
005, color, purple
; 
run;

Ideally, I would want it so it looks like:

id category val1 val2 val3

001 color blue red

002 color orange

003 color red orange blue

004 color aqua

005 color purple

Thanks

Re: How do I transpose a data set that has multiple values for an ID variable?

Tom — Tue, 23 Aug 2022 14:31:06 GMT

PROC TRANSPOSE can handle that example very easily. Perhaps you over simplified your example?

data have1;
	input id $ category $ val $;
datalines;
001  color  blue
001  color  red
002  color  orange
003  color  red
003  color  orange 
003  color  blue
004  color  aqua
005  color  purple
; 

proc transpose data=have1 out=want1(drop=_name_) prefix=VAL ;
  by id category ;
  var val;
run;

Result

Obs    id     category    VAL1      VAL2      VAL3

 1     001     color      blue      red
 2     002     color      orange
 3     003     color      red       orange    blue
 4     004     color      aqua
 5     005     color      purple

Re: How do I transpose a data set that has multiple values for an ID variable?

Hello_there — Tue, 23 Aug 2022 14:32:45 GMT

Thanks!

Re: How do I transpose a data set that has multiple values for an ID variable?

ballardw — Tue, 23 Aug 2022 14:35:45 GMT

For your given example:

proc transpose data=have1 out=trans (drop=_name_)
  prefix=val;
   by id category;
   var val;
run;

Transpose uses the BY variables to create groupings.

The default behavior is to use the Prefix (defaults to col) as the name of the transposed variables with an increment for number of values in the by group.

If you were concerned with the ID statement that is for a variable used to NAME output variables and generally doesn't allow multiple values per By group.

Re: How do I transpose a data set that has multiple values for an ID variable?

Hello_there — Tue, 23 Aug 2022 14:36:52 GMT

Thanks for your reply!