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.
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;
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.
What is the reason for having two observations for ID 0001?
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.
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;
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.