How to Create the Additional Records in the Data Based on Existed Data.

[SASuserlot]

I am sorry , If my title is confusing. I am came up with the title best of my ability.  I am looking to create an additional record easy way  based on the data available.

For example:  In my data,  subjid =101 and Visit= V2, missing the  PARAMCD= BMI and same applies to the subjid=103 at visit= V1.

So I am looking is there any easy way, I can check my subjects  missing PARAMCD='BMI' at a particular visit and if it missing at a particular Visit, I need to calculate the BMI ( BMI= wit/(hit*hit))?

data vital;
input subjid visit$ paramcd$ aval ;
cards;
101 V1 hit 1.60 
101 V1 wit 80 
101 V1 BMI 31.2
101 V2 hit 1.60 
101 V2 wit 90 
103 V1 hit 1.502 
103 V1 wit 74 
103 V2 hit 1.502 
103 V2 wit 75 
103 V2 BMI 33.2 
;
RUN;

[andreas_lds · Accepted solution]

This task can be solved with a single data step, but you need to be sure that BMI is always the last obs in each visit. Unfortunately i don't see a way to ensure this by code.

data want_one;
   set sorted;
   by subjid visit;

   retain _hit _wit;

   if first.visit then do;
      call missing(_hit, _wit);
   end;

   select (lowcase(paramcd));
      when ('hit') _hit = aval;
      when ('wit') _wit = aval;
      otherwise;
   end;

   output;

   if last.visit and lowcase(paramcd) ^= 'bmi' then do;
      paramcd = 'BMI';
      aval = _wit / (_hit * _hit);
      output;
   end;

   drop _:;
run;

Another idea, with longer runtime:

proc transpose data=sorted out=transposed(drop=_name_);
   by subjid visit;
   var aval;
   id paramcd;
run;

data filled;
   set transposed;

   if missing(bmi) then do;
      bmi = wit / (hit * hit);
   end;
run;

proc transpose data=filled out=want_transpose(rename= (col1 = aval)) name=paramcd;
   by subjid visit;
   var hit wit bmi;
run;

[SASuserlot]

Thank you @andreas_lds . It worked and do the job I wanted.  Thank you for pointing out on BMI being the last in Each Visit.  I can assign the Numeric numbers to the paramcds and Make sure it is always at the last. 

[PeterClemmensen]

Is it safe to assume that hit / wit is always there ?

[SASuserlot]

Good question.  For my actual data the answer is 'Yes' because we only keeping the records that have the both at a particular visit.

[Ksharp]

data vital;
input subjid visit$ paramcd$ aval ;
cards;
101 V1 hit 1.60 
101 V1 wit 80 
101 V1 BMI 31.2
101 V2 hit 1.60 
101 V2 wit 90 
103 V1 hit 1.502 
103 V1 wit 74 
103 V2 hit 1.502 
103 V2 wit 75 
103 V2 BMI 33.2 
;
RUN;

proc sql;
create table want as
select a.*,b.aval as aval
 from (select * from
(select distinct subjid from vital),
(select distinct visit from vital),
(select distinct paramcd from vital)
) as a
natural left join vital as b;
quit;

[SASuserlot]

Thank you for your suggestion @Ksharp .  I think you missing to Include the BMI calculation in the code, I am getting missing values ( AVAL)  for the Records (BMI) Newly created. and I also see a note in the log, Is it gonna run into any issues?

NOTE: The execution of this query involves performing one or more Cartesian product joins that can not be optimized.

[Ksharp]

After getting WANT dataset ,it is very easy to get BMI.

data vital;
input subjid visit$ paramcd$ aval ;
cards;
101 V1 hit 1.60 
101 V1 wit 80 
101 V1 BMI 31.2
101 V2 hit 1.60 
101 V2 wit 90 
103 V1 hit 1.502 
103 V1 wit 74 
103 V2 hit 1.502 
103 V2 wit 75 
103 V2 BMI 33.2 
;
RUN;

proc sql;
create table want as
select a.*,b.aval as aval
 from (select * from
(select distinct subjid from vital),
(select distinct visit from vital),
(select distinct paramcd from vital)
) as a
natural left join vital as b;
quit;
data want;
 merge want want(keep=aval rename=(aval=hit) firstobs=2 ) want(keep=aval rename=(aval=wit) firstobs=3 );
 if paramcd='BMI' and missing(aval) then aval=wit/hit**2;
 drop hit wit ;
run;

[SASuserlot]

Thank you.