Hi All,
the below is my have dataset. for each subject ha multiple vis and each vis have dos.
i want to create ddos, wkdos
ddos is nothing but , dos having at that particular vis,
wdos is sum of ddos in that paricular vis
cumdos is sum of wdos, maxwkdos and minwkdos are max and minimum value of wdos
Have:
sub vis dos
100 WEEK 1 5
100 WEEK 1 5
100 WEEK 1 5
100 WEEK 2
100 WEEK 2 5
100 WEEK 2 5
100 WEEK 3 5
100 WEEK 3 5
100 WEEK 3 5
100 WEEK 4 5
100 WEEK 4
100 WEEK 4 5
100 WEEK 5 5
100 WEEK 5 5
100 WEEK 5
Want:
sub group vis final
100 DDOS WEEK 1 5.000
100 DDOS WEEK 1 5.000
100 DDOS WEEK 1 5.000
100 WDOS WEEK 1 15.000
100 DDOS WEEK 2
100 DDOS WEEK 2 5.000
100 DDOS WEEK 2 5.000
100 WDOS WEEK 2 10.000
100 DDOS WEEK 3 5.000
100 DDOS WEEK 3 5.000
100 DDOS WEEK 3 5.000
100 WDOS WEEK 3 15.000
100 DDOS WEEK 4 5.000
100 DDOS WEEK 4
100 DDOS WEEK 4 5.000
100 WDOS WEEK 4 10.000
100 DDOS WEEK 5 5.000
100 DDOS WEEK 5 5.000
100 DDOS WEEK 5
100 WDOS WEEK 5 10.000
100 CUMDOS 60.000
100 MAXWKDOS 15.000
100 MINWKDOS 10.000
my code:
proc sql;
create table want as
select distinct sub,vis,sum(dos) as final,'WDOS' as group
from have
group by sub,vis
order by sub, final;
quit;
this way i am creating separately for all group parameters, any better way like keep a macro or using array or dataset to achieve want
Thank you
sam
If your data is structured the way you've shown you need a data step.
It's not a data structure that I would ever recommend though.
data want;
set have;
by sub vis ;
length group $8.;
retain weekly_dose 0 cum_dose 0 maxwdos 0 minwdos 9999999;
if first.vis then weekly_dose=dos;
else weekly_dose=sum(dos,weekly_dose);
group="DDOS";
output;
if last.vis then do;
group="WDOS";
dos=weekly_dose;
cum_dose=weekly_dose+cum_dose;
maxwdos=max(weekly_dose, maxwdos);
minwdos=min(weekly_dose, minwdos);
output;
end;
if last.sub then do;
group="CUMWDOS";
dos=cum_dose;
output;
group="MAXWDOS";
dos=maxwdos;
output;
group="MINWDOS";
dos=minwdos;
output;
end;
run;
Thanks Reeza!!!
I have an issue with cum_dose counts. For sub=100 the cum_dos is correct but for other subjects also it is summing the previous cum_dos.
in my final dataset for CUMWDOS
want:
subj
100 CUMWDOS 60
101 CUMWDOS 55
but the above code getting
subj
100 CUMWDOS 60
101 CUMWDOS 115
Thanks
Sam
data have; infile cards truncover; input sub vis & $10. dos ; cards; 100 WEEK 1 5 100 WEEK 1 5 100 WEEK 1 5 100 WEEK 2 100 WEEK 2 5 100 WEEK 2 5 100 WEEK 3 5 100 WEEK 3 5 100 WEEK 3 5 100 WEEK 4 5 100 WEEK 4 100 WEEK 4 5 100 WEEK 5 5 100 WEEK 5 5 100 WEEK 5 ; run; data want; set have; by sub vis; retain group 'DDOS '; retain MAXWKDOS MINWKDOS ; output; wdos+dos; if last.vis then do; group='WDOS ';dos=wdos;output; CUMDOS+wdos;MAXWKDOS=max(MAXWKDOS,wdos); MINWKDOS =min(MINWKDOS,wdos); group='DDOS';wdos=0; end; if last.sub then do; call missing(vis); group= 'CUMDOS ';dos=CUMDOS;output; group='MAXWKDOS';dos=MAXWKDOS;output; group='MINWKDOS';dos=MINWKDOS;output; CUMDOS=0; MAXWKDOS=0; MINWKDOS=0; end; drop CUMDOS MAXWKDOS MINWKDOS wdos; run;
Xia Keshan
Hi Keshan,
Thanks for your code!!! your code is also producing the same issue.
Except CUMDOS rest are working fine. I want cumulative dos with in each subject.
For example:
For subj 100:
Cumdos is sum of(wdos) : like week1+week2+week3+week4+week5=(15+10+15+10+10)=60
For subj 101:
Cumdos is sum of(wdos) : like week1+week2+week3+week4+week5=(10+15+15+25+15)=80 (but the above logic is getting 60+80=140)
i do not want 140 as cumdos for sub 101 , the correct value is 80.
Thanks
Sam
What does your data look like ?
data have; infile cards truncover; input sub vis & $10. dos ; cards; 100 WEEK 1 5 100 WEEK 1 5 100 WEEK 1 5 100 WEEK 2 100 WEEK 2 5 100 WEEK 2 5 100 WEEK 3 5 100 WEEK 3 5 100 WEEK 3 5 100 WEEK 4 5 100 WEEK 4 100 WEEK 4 5 100 WEEK 5 5 100 WEEK 5 5 100 WEEK 5 101 WEEK 1 5 101 WEEK 1 5 101 WEEK 1 5 101 WEEK 2 101 WEEK 2 5 101 WEEK 2 5 101 WEEK 3 5 101 WEEK 3 5 101 WEEK 3 5 101 WEEK 4 5 101 WEEK 4 101 WEEK 4 5 101 WEEK 5 5 101 WEEK 5 5 101 WEEK 5 ; run; data want; set have; by sub vis; retain group 'DDOS '; retain MAXWKDOS MINWKDOS ; output; wdos+dos; if last.vis then do; group='WDOS ';dos=wdos;output; CUMDOS+wdos;MAXWKDOS=max(MAXWKDOS,wdos); MINWKDOS =min(MINWKDOS,wdos); group='DDOS';wdos=0; end; if last.sub then do; call missing(vis); group= 'CUMDOS ';dos=CUMDOS;output; group='MAXWKDOS';dos=MAXWKDOS;output; group='MINWKDOS';dos=MINWKDOS;output; CUMDOS=0; MAXWKDOS=0; MINWKDOS=999999;group='DDOS';wdos=0; end; drop CUMDOS MAXWKDOS MINWKDOS wdos; run;
Xia Keshan
Thank you ksharp,
data have;
infile cards truncover;
input sub vis & $10. dos ;
cards;
100 WEEK 1 5
100 WEEK 1 5
100 WEEK 1 5
100 WEEK 2
100 WEEK 2 5
100 WEEK 2 5
100 WEEK 3 5
100 WEEK 3 5
100 WEEK 3 5
100 WEEK 4 5
100 WEEK 4
100 WEEK 4 5
100 WEEK 5 5
100 WEEK 5 5
100 WEEK 5
101 WEEK 1
101 WEEK 1
101 WEEK 1
101 WEEK 2
101 WEEK 2
;
run;
proc sort data=have;
by sub vis;
run;
data want;
set have;
by sub vis;
retain group 'DDOS ';
retain MAXWKDOS MINWKDOS ;
output;
wdos+dos;
if last.vis then do;
group='WDOS ';dos=wdos;output;
CUMDOS+wdos;MAXWKDOS=max(MAXWKDOS,wdos); MINWKDOS =min(MINWKDOS,wdos);
group='DDOS';wdos=0;
end;
if last.sub then do; call missing(vis);
group= 'CUMDOS ';dos=CUMDOS;output;
group='MAXWKDOS';dos=MAXWKDOS;output;
group='MINWKDOS';dos=MINWKDOS;output;
CUMDOS=0; MAXWKDOS=0; MINWKDOS=999999;group='DDOS';wdos=0;
end;
drop CUMDOS MAXWKDOS MINWKDOS wdos;
run;
I do not want the highlighted part in my final data set.Because this record does not have any dos. If subj have missing dos then i need to keep only sub with group DDOS.In the above example then non highlighted part should be in data set for sub 101
Thanks
Sam
OK. Assuming dose should never be a negative value .
data have; infile cards truncover; input sub vis & $10. dos ; cards; 100 WEEK 1 5 100 WEEK 1 5 100 WEEK 1 5 100 WEEK 2 100 WEEK 2 5 100 WEEK 2 5 100 WEEK 3 5 100 WEEK 3 5 100 WEEK 3 5 100 WEEK 4 5 100 WEEK 4 100 WEEK 4 5 100 WEEK 5 5 100 WEEK 5 5 100 WEEK 5 101 WEEK 1 101 WEEK 1 101 WEEK 1 101 WEEK 2 101 WEEK 2 ; run; data want; set have; by sub vis; retain group 'DDOS '; retain MAXWKDOS MINWKDOS ; output; wdos+dos; if last.vis then do; group='WDOS ';dos=wdos;if dos gt 0 then output; CUMDOS+wdos;MAXWKDOS=max(MAXWKDOS,wdos); MINWKDOS =min(MINWKDOS,wdos); group='DDOS';wdos=0; end; if last.sub then do; call missing(vis); group= 'CUMDOS ';dos=CUMDOS; if dos gt 0 then output; group='MAXWKDOS';dos=MAXWKDOS; if dos gt 0 then output; group='MINWKDOS';dos=MINWKDOS; if dos gt 0 then output; CUMDOS=0; MAXWKDOS=0; MINWKDOS=999999;group='DDOS';wdos=0; end; drop CUMDOS MAXWKDOS MINWKDOS wdos; run;
Xia Keshan
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.