This works, let us know if it worked for you. data have;
infile datalines dlm=',' dsd;
input @1 ssn:$9. @11 name:$11. @23 test_name:$17. @41 value:best12.;
datalines;
123456789 john smith height(feet) 4
123456789 john smith height(inches) 11.5
123456789 john smith BMI 20
123456788 john doe height(feet) 5
123456788 john doe height(inches) 12
123456788 john doe BMI 25
;
run;
proc sql;
create table want as
select ssn, name, test_name, value from have
where test_name not in('height(feet)','height(inches)')
union
select a.ssn, a.name,'height(feet)' as test_name, (12*a.value+b.value) as value
from (select ssn, name, test_name, value
from have where test_name='height(feet)') as a,
(select ssn, name, test_name, value
from have where test_name='height(inches)') as b
where a.ssn=b.ssn
;
quit;
... View more
