CODEWITHSUNDEEP
sas
Shawn Brar
Shawn Brar

Reputation: 1420

Turn rows into variables in a dataset

I have a data set as following:-

data club;
input Name $ Gov_Type $ YR1 YR2 YR3;
datalines;
Afg COC 10 20 30
Afg GE 20 30 40
Afg PS 10 3 202
Afg RQ . 30 10
Brh COC 10 . 30
Brh GE 4 12 33
Brh PS 12 43 12
Brh RQ 19 3 12
Gen COC 32 . 65
Gen GE 13 93 44
Gen PS 12 38 12
Gen RQ 13 1 13
;

I want to change it so that COC, GE, PS and RQ become variables and have the value of YR1, YR2 and YR3 are displayed as following dataset:-

data club2;
input Name $ YR $ COC GE PS RQ;
datalines;
Afg YR1 10 20 10 . 
Afg YR2 20 30 3  30
Afg YR3 30 40 202 10
Brh YR1 10 4 12 9
Brh YR2 . 12 43 3
Brh YR3 30 33 12 12
Gen YR1 32 13 12 13
Gen YR2 . 93 38 1
Gen YR3 65 44 12 13
;

How will I be able to this?

Thanks in advance.

Upvotes: 0

Views: 54

Answers (2)

Richard
Richard

Reputation: 27498

The desired data transformation is a easily accomplished with Proc TRANSPOSE.

proc transpose data=club out=stage(rename=_name_=YR);
  by name;
  id Gov_type;
run;

enter image description here

If the transform is for reporting purposes considering using Proc TABULATE

proc tabulate data=club;
  class name gov_type;
  var yr1-yr3;
  table name=''*(yr1-yr3)*sum=' '*f=9.,gov_type;
run;

enter image description here

Upvotes: 1

PeterClemmensen
PeterClemmensen

Reputation: 4937

Try this

data club;
input Name $ Gov_Type $ YR1 YR2 YR3;
datalines;
Afg COC 10 20 30
Afg GE 20 30 40
Afg PS 10 3 202
Afg RQ . 30 10
Brh COC 10 . 30
Brh GE 4 12 33
Brh PS 12 43 12
Brh RQ 19 3 12
Gen COC 32 . 65
Gen GE 13 93 44
Gen PS 12 38 12
Gen RQ 13 1 13
;

data temp;
   set club;
   array y yr:;
   do over y;
      yr = y;
      v = vname(y);
      output;
   end;
   drop yr1-yr3;
run;

proc sort data = temp;
   by Name v Gov_Type;
run;

data club2;
   do i = 1 by 1 until (last.v);
      set temp;
      by Name v;
      array g{*} coc ge ps rq;
      if upcase(Gov_Type) = upcase(vname(g[i])) then g[i] = yr;
   end;
   drop i yr Gov_Type;
run;

Upvotes: 0

Related Questions