CODEWITHSUNDEEP
sastranspose
Andrew Myers
Andrew Myers

Reputation: 25

SAS 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, preferably with proc transpose or sql?

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

Upvotes: 0

Views: 87

Answers (1)

DWal
DWal

Reputation: 2762

Using proc transpose is the obvious solution.

proc transpose data=yourdata out=yourdatat1(drop=_name_);
  by pers year month;
  id variable;
  var value;
run;

Using proc sql, you can use case when logic to summarize the data like below:

proc sql;
create table yourdatat2 as
select
  pers,
  year,
  month,
  sum(case when variable = 'Var1' then value else . end) as Var1,
  sum(case when variable = 'Var2' then value else . end) as Var2
from
  yourdata
group by
  pers,
  year,
  month
;
quit;

Upvotes: 4

Related Questions