Transpose data for two columns based on two sectors

[abraham1]

Hi All,

The below test data contain information of two sectors (emea, fda).
I want to isolate the datasets for score and event variable based on individual sectors (value for col E-F as 'emea', newly added col G-H as 'fda')

e.g. for record 3, 6,7,8 --> value populated from E-F where as blank for G-H
for record 4,5 --> value populated from G-H where as blank for E-F

Can you please help.

data test;
  input pid age gender race event2 $ sector $ score;
cards;
101 21 1 1 fever EMEA 25
102 21 2 2 pain FDA 45 
103 31 1 3 headac FDA 54
104 43 2 4 joint EMEA 66
105 43 2 5 cold EMEA 100
106 75 2 6 anemia EMEA 232
;
run;

Expected output

[Tom · Accepted solution]

You can produce that report without changing the dataset.

proc report data=test;
column pid age gender race sector,(event2 score);
define pid / group;
define age/group;
define gender/group;
define race/group;
define sector/across;
run;