DATA Step, Macro, Functions and more

SAS how to tranpose long data that has multiple variables and values per group id?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

SAS how to tranpose long data that has multiple variables and values per group id?

I have data that is set up like this:

Pers Year Month Variable Value 
AAA 2001 01 Var1 100
AAA 2001 01 Var2 200
AAA 2001 06 Var1 110
AAA 2001 06 Var2 210
AAA 2002 01 Var1 120
AAA 2002 01 Var2 .
BBB 2001 01 Var1 100
BBB 2001 01 Var2 200
BBB 2001 06 Var1 110
BBB 2001 06 Var2 210
BBB 2002 01 Var2 220

I would like data that looks like this:

Pers Year Month Var1 Var2 
AAA 2001 01 100 200
AAA 2001 06 110 210
AAA 2002 01 120 .
BBB 2001 01 100 200
BBB 2001 06 110 210
BBB 2002 01 . 220

How can I do this in SAS?

Note that in the input data, above, Person BBB is missing an observation for 2002-01 Var1, but the output data has returned a missing value in the last line, i.e. ".".


Accepted Solutions
Solution
‎02-23-2015 01:40 PM
SAS Super FREQ
Posts: 684

Re: SAS how to tranpose long data that has multiple variables and values per group id?

Hi

You can use the TRANSPOSE procedure to do this. See example below:

data have;
  infile cards dlm=",";
 
input
    pers $ Year Month Variable $ Value
  ;
cards;
AAA,2001,01,Var1,100
AAA,2001,01,Var2,200
AAA,2001,06,Var1,110
AAA,2001,06,Var2,210
AAA,2002,01,Var1,120
AAA,2002,01,Var2,.
BBB,2001,01,Var1,100
BBB,2001,01,Var2,200
BBB,2001,06,Var1,110
BBB,2001,06,Var2,210
BBB,2002,01,Var2,220
;
proc sort data=have;
  by pers year month;
run;

proc transpose
 
data=have
 
out=want(drop=_name_)
;
  by pers year month;
  id Variable;
  var Value;
run;

View solution in original post


All Replies
Solution
‎02-23-2015 01:40 PM
SAS Super FREQ
Posts: 684

Re: SAS how to tranpose long data that has multiple variables and values per group id?

Hi

You can use the TRANSPOSE procedure to do this. See example below:

data have;
  infile cards dlm=",";
 
input
    pers $ Year Month Variable $ Value
  ;
cards;
AAA,2001,01,Var1,100
AAA,2001,01,Var2,200
AAA,2001,06,Var1,110
AAA,2001,06,Var2,210
AAA,2002,01,Var1,120
AAA,2002,01,Var2,.
BBB,2001,01,Var1,100
BBB,2001,01,Var2,200
BBB,2001,06,Var1,110
BBB,2001,06,Var2,210
BBB,2002,01,Var2,220
;
proc sort data=have;
  by pers year month;
run;

proc transpose
 
data=have
 
out=want(drop=_name_)
;
  by pers year month;
  id Variable;
  var Value;
run;
Trusted Advisor
Posts: 1,131

Re: SAS how to tranpose long data that has multiple variables and values per group id?

Hi Andrew,

Alternatively the same output could be obtained by arrays.

data have;

input Pers$ Year Month Variable$ Value ;

ord=input(compress(variable,,'kd'),1.);

cards;

AAA 2001 01 Var1 100

AAA 2001 01 Var2 200

AAA 2001 06 Var1 110

AAA 2001 06 Var2 210

AAA 2002 01 Var1 120

AAA 2002 01 Var2 .

BBB 2001 01 Var1 100

BBB 2001 01 Var2 200

BBB 2001 06 Var1 110

BBB 2001 06 Var2 210

BBB 2002 01 Var2 220

;

proc sort data=have;

by pers year month variable;

run;

data want;

set have;

retain var1 var2;

  by pers year month variable;

  array vs(2) var1 var2;

  if first.month then do;

  do i = 1 to 2;

  vs(i)=.;

  end;

  end;

  vs(ord)=value;

  if last.month;

run;

Hope it helps.

Thanks,

Jag

Thanks,
Jag
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 220 views
  • 3 likes
  • 3 in conversation