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

[AndrewMyers]

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

[BrunoMueller · Accepted solution]

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;

[Jagadishkatam]

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