topic Re: How to Create the Additional Records in the Data Based on Existed Data. in SAS Programming

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

SASuserlot — Mon, 12 Sep 2022 21:16:13 GMT

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;

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

andreas_lds — Tue, 13 Sep 2022 06:23:30 GMT

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;

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

PeterClemmensen — Tue, 13 Sep 2022 09:17:22 GMT

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

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

Ksharp — Tue, 13 Sep 2022 12:01:16 GMT

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;

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

SASuserlot — Thu, 15 Sep 2022 15:16:30 GMT

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

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

SASuserlot — Thu, 15 Sep 2022 15:30:18 GMT

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.

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

SASuserlot — Thu, 15 Sep 2022 15:33:37 GMT

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.

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

Ksharp — Fri, 16 Sep 2022 11:51:00 GMT

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;

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

SASuserlot — Fri, 23 Sep 2022 15:14:59 GMT

Thank you.