Transposing

[sandrube]

Hello dear friend,

Wish you all Happy Holidays and Happy New Year!

I have the following data:

data new;
infile cards missover;
format FEMALE 12. YEAR 12. RACE 12. pcnt $ 32.;
input FEMALE YEAR RACE pcnt;
cards;
0 2018 . 12900762(42.6%)
0 2019 . 12978685(43.0%)
1 2018 . 17356510(57.4%)
1 2019 . 17236228(57.0%)
. 2018 1 19747204(66.9%)
. 2019 1 19851043(67.2%)
. 2018 2 4453586(15.1%)
. 2019 2 4519150(15.3%)
. 2018 3 3394854(11.5%)
. 2019 3 3262700(11.1%)
. 2018 4 822329(2.8%)
. 2019 4 826270(2.8%)
. 2018 5 191580(0.6%)
. 2019 5 201155(0.7%)
. 2018 6 893325(3.0%)
. 2019 6 858436(2.9%)
;

I am trying to transpose it to following:

data new;
infile cards missover;
format Variable $ 32. Y_2018 $ 32. Y_2019 $ 32.;
input Variable Y_2018 Y_2019;
cards;
FEMALE_0 12900762(42.6%) 12978685(43.0%)
FEMALE_1 17356510(57.4%) 17236228(57.0%)
RACE_1 19747204(66.9%) 19851043(67.2%)
RACE_2 4453586(15.1%) 4519150(15.3%)
RACE_3 3394854(11.5%) 3262700(11.1%)
RACE_4 822329(2.8%) 826270(2.8%)
RACE_5 191580(0.6%) 201155(0.7%)
RACE_6 893325(3.0%) 858436(2.9%)
;

I am struggling with creating the "Variable" and transpose. Could you please guide or help me.

Thanks

Rube

[Reeza · Accepted solution]

Create Variable using an IF/THEN condition followed by a standard transpose.

data new_id;
set new;
if not missing(female) then category = catx("_", 'Female', female);
else category = catx("_", "RACE", race);

run;

proc sort data=new_id;
by category;
run;

proc transpose data=new_id out=want prefix=Y_;
by category;
id year;
var pcnt;
run;

---

@sandrube wrote:

Hello dear friend,

Wish you all Happy Holidays and Happy New Year!

I have the following data:

data new;
infile cards missover;
format FEMALE 12. YEAR 12. RACE 12. pcnt $ 32.;
input FEMALE YEAR RACE pcnt;
cards;
0 2018 . 12900762(42.6%)
0 2019 . 12978685(43.0%)
1 2018 . 17356510(57.4%)
1 2019 . 17236228(57.0%)
. 2018 1 19747204(66.9%)
. 2019 1 19851043(67.2%)
. 2018 2 4453586(15.1%)
. 2019 2 4519150(15.3%)
. 2018 3 3394854(11.5%)
. 2019 3 3262700(11.1%)
. 2018 4 822329(2.8%)
. 2019 4 826270(2.8%)
. 2018 5 191580(0.6%)
. 2019 5 201155(0.7%)
. 2018 6 893325(3.0%)
. 2019 6 858436(2.9%)
;
run;

I am trying to transpose it to following:

data new;
infile cards missover;
format Variable $ 32. Y_2018 $ 32. Y_2019 $ 32.;
input Variable Y_2018 Y_2019;
cards;
FEMALE_0 12900762(42.6%) 12978685(43.0%)
FEMALE_1 17356510(57.4%) 17236228(57.0%)
RACE_1 19747204(66.9%) 19851043(67.2%)
RACE_2 4453586(15.1%) 4519150(15.3%)
RACE_3 3394854(11.5%) 3262700(11.1%)
RACE_4 822329(2.8%) 826270(2.8%)
RACE_5 191580(0.6%) 201155(0.7%)
RACE_6 893325(3.0%) 858436(2.9%)
;
run;

I am struggling with creating the "Variable" and transpose. Could you please guide or help me.

Thanks

Rube

---

[sandrube]

Ok for removing "run;"

Thank you very much for the explanation.

Rube

[Tom]

Looks like some of the rows have FEMALE populated and the others have RACE populated.

So test if those variables are missing or not to determine how to create value for VARIABLE.

data have;
  length FEMALE 8 YEAR 8 RACE 8 pcnt $32;
  input FEMALE YEAR RACE pcnt;
cards;
0 2018 . 12900762(42.6%)
0 2019 . 12978685(43.0%)
1 2018 . 17356510(57.4%)
1 2019 . 17236228(57.0%)
. 2018 1 19747204(66.9%)
. 2019 1 19851043(67.2%)
. 2018 2 4453586(15.1%)
. 2019 2 4519150(15.3%)
. 2018 3 3394854(11.5%)
. 2019 3 3262700(11.1%)
. 2018 4 822329(2.8%)
. 2019 4 826270(2.8%)
. 2018 5 191580(0.6%)
. 2019 5 201155(0.7%)
. 2018 6 893325(3.0%)
. 2019 6 858436(2.9%)
;

data for_transpose;
  set have;
  length variable $32 ;
  if not missing(female) then variable=catx('_','FEMALE',female);
  else if not missing(race) then variable=catx('_','RACE',race);
run;

proc transpose data=for_transpose out=want(drop=_name_) prefix=Y_;
  by variable notsorted;
  id year ;
  var pcnt;
run;

Result

Obs    variable        Y_2018             Y_2019

 1     FEMALE_0    12900762(42.6%)    12978685(43.0%)
 2     FEMALE_1    17356510(57.4%)    17236228(57.0%)
 3     RACE_1      19747204(66.9%)    19851043(67.2%)
 4     RACE_2      4453586(15.1%)     4519150(15.3%)
 5     RACE_3      3394854(11.5%)     3262700(11.1%)
 6     RACE_4      822329(2.8%)       826270(2.8%)
 7     RACE_5      191580(0.6%)       201155(0.7%)
 8     RACE_6      893325(3.0%)       858436(2.9%)

[sandrube]

Thank you very much!

Rube