BookmarkSubscribeRSS Feed
sam369
Obsidian | Level 7

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


7 REPLIES 7
Reeza
Super User

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;

sam369
Obsidian | Level 7

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

Ksharp
Super User
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

sam369
Obsidian | Level 7

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

Ksharp
Super User

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

sam369
Obsidian | Level 7

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;

Capture.PNG

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

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1169 views
  • 0 likes
  • 3 in conversation