sas - Turn rows into variables in a dataset

Question

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.

question from:https://stackoverflow.com/questions/65880027/turn-rows-into-variables-in-a-dataset

Answer 1

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;

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;