Help using Base SAS procedures

macro or array

Reply
Regular Contributor
Posts: 168

macro or array

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


Super User
Posts: 17,868

Re: macro or array

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;

Regular Contributor
Posts: 168

Re: macro or array

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

Super User
Posts: 9,682

Re: macro or array

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

Regular Contributor
Posts: 168

Re: macro or array

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

Super User
Posts: 9,682

Re: macro or array

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

Regular Contributor
Posts: 168

Re: macro or array

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

Super User
Posts: 9,682

Re: macro or array

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

Ask a Question
Discussion stats
  • 7 replies
  • 451 views
  • 0 likes
  • 3 in conversation