topic Re: Fill with values a set of variables to reach the total in New SAS User

Fill with values a set of variables to reach the total

NewUsrStat — Mon, 16 Dec 2024 17:48:28 GMT

Hi guys,

suppose to have the following dataset:

data DB;
  input ID :$20. Comorbidity Total Class1 Class2 Class3 Class4 Class5 Class7;
cards;
0001 1 1.34    0.2     .      .     .     .    .    .
0001 1  .       .    0.5      .     .     .    .    .
0002 0  15      .      .      1.2   .     .    .    .  
0003 1  2       .      .      .    0.5     .    .    .
; 
run;

Is there a way to get the following?

data DB1;
  input ID :$20. Comorbidity Total Class1 Class2 Class3 Class4 Class5 Class7;
cards;
0001 1 1.34    0.2     .      .     .     .    .    .
0001 1  .       .    0.5      0.64  .     .    .    .
0002 0  15      .      .      1.2   5     5    3.8  .  
0003 1  2       .      .      .    0.5   1.5    .   .
; 
run;

In other words, the Total should be reached by adding values from the last filled class on -->.

Classes are age classes and values are persons-years. Total is the total a patient is in the study. So if a patient stay in the (age)class x a time y that does not cover the Total, additional person-years must be added from the age class x-->on because age can increase only. The reason why I need to fill with values is because the patient has not an event and this justify why Class* are empty but it is still at risk. Moreover each Class* cannot have values >5 because age classes are in a range of 5 years.

Thank you in advance.

Re: Fill with values a set of variables to reach the total

ballardw — Mon, 16 Dec 2024 18:08:44 GMT

This sounds like something that should have been added before reducing everything to "classes". As in when the data was person-year and could be added.

Since this is again apparently derived from data like the following as posted in https://communities.sas.com/t5/New-SAS-User/From-long-format-to-short-data-format/m-p/953512#M42861

data DB;
  input ID :$20. Admission :date09. Discharge :date09. Class Value; 
  format Admission date9. Discharge date9.;
cards;
0001 13JAN2015 20JAN2015 2  0.32
0001 21FEB2015 31DEC2015 2  0.32
0001 01MAR2018 30SEP2018 3  0.43
0001 01JAN2019 31DEC2019 3  0.43
0002 01JAN2015 31DEC2015 2  1.92
0002 01JAN2019 31OCT2019 2  1.92
0003 08FEB2014 10MAR2014 1  4.32
0003 16JUN2015 13JUL2015 1  4.32
0004 04MAY2016 10MAY2016 3  3.22
0004 13SEP2017 15NOV2017 3  3.22
0004 09DEC2018 31DEC2018 3  3.22
;

perhaps the assignment of "class" and/or Value here are appropriate discussions. It seems like the censoring of non-event data has added complexity to whatever analysis this should be.

And the rule if class7 does have a value but the total isn't reached is what?

And are your sure Class stops at 7? 5 year intervals means that class20 isn't impossible, unlikely perhaps but not impossible.

Re: Fill with values a set of variables to reach the total

NewUsrStat — Mon, 16 Dec 2024 19:23:15 GMT

Thank you very much for your comment. Unfortunately I cannot manage it before because it means to add new rows to the original matrix corresponding to age classes without the event and that should allow to reach the total. I do not know (prior) how many classes a patient needs to reach the total.

Re: Fill with values a set of variables to reach the total

Kurt_Bremser — Mon, 16 Dec 2024 21:41:24 GMT

What is the reason for having two observations for ID 0001?

Re: Fill with values a set of variables to reach the total

NewUsrStat — Mon, 16 Dec 2024 21:50:14 GMT

Thank you very much for your comment.The patient has comorbidities at two different admissions although this information is not shown for simplicity. I reported only "comorbidity" variable. There are many patients with more than 1 record.

Re: Fill with values a set of variables to reach the total

Kurt_Bremser — Mon, 16 Dec 2024 22:13:33 GMT

Do not hide information. Let us see all the facts that make up your data so we can work with it.

Otherwise this will only be a Maxim 42 issue.

Re: Fill with values a set of variables to reach the total

Ksharp — Tue, 17 Dec 2024 06:14:15 GMT

data DB;
  input ID :$20. Comorbidity Total Class1 Class2 Class3 Class4 Class5 Class7;
cards;
0001 1 1.34    0.2     .      .     .     .    .    .
0001 1  .       .    0.5      .     .     .    .    .
0002 0  15      .      .      1.2   .     .    .    .  
0003 1  2       .      .      .    0.5     .    .    .
; 
run;
proc summary data=DB nway;
class id;
var total class: ;
output out=temp sum=;
run;
proc transpose data=temp out=temp2;
by id total;
var class: ;
run;
data temp3;
 set temp2;
 by id;
 if first.id then sum=0;
 sum+col1;
 flag=missing(col1);
run;
data temp4;
do until(last.flag);
 set temp3;
 by id flag notsorted;
 if last.id then found=1;
end;
rest=total-sum;
do until(last.flag);
 set temp3;
 by id flag notsorted;
 if found then do;
   if rest<=5 then do;col1=rest;rest=.;end;
    else do;col1=5;rest=rest-5;end;
 end;
 output;
end;
run;
proc transpose data=temp4 out=temp5(drop=_name_);
by id;
var col1;
id _name_;
run;

data want;
 set DB;
 by id ;
 if last.id then set temp5;
run;