topic Re: macro or array in SAS Procedures

macro or array

sam369 — Wed, 04 Jun 2014 19:46:16 GMT

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 2

100 WEEK 2 5

100 WEEK 3 5

100 WEEK 4 5

100 WEEK 4

100 WEEK 4 5

100 WEEK 5 5

100 WEEK 5

Want:

sub group vis final

100 DDOS WEEK 1 5.000

100 WDOS WEEK 1 15.000

100 DDOS WEEK 2

100 DDOS WEEK 2 5.000

100 WDOS WEEK 2 10.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

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

Re: macro or array

Reeza — Wed, 04 Jun 2014 22:05:52 GMT

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;

Re: macro or array

sam369 — Thu, 05 Jun 2014 03:07:29 GMT

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

Re: macro or array

Ksharp — Thu, 05 Jun 2014 13:16:53 GMT

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

Re: macro or array

sam369 — Thu, 05 Jun 2014 13:54:08 GMT

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

Re: macro or array

Ksharp — Thu, 05 Jun 2014 14:27:05 GMT

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

Re: macro or array

sam369 — Thu, 05 Jun 2014 20:07:40 GMT

Thank you ksharp,

data have;

infile cards truncover;

input sub vis & $10. dos ;

cards;

100 WEEK 1 5

100 WEEK 2

100 WEEK 2 5

100 WEEK 3 5

100 WEEK 4 5

100 WEEK 4

100 WEEK 4 5

100 WEEK 5 5

100 WEEK 5

101 WEEK 1

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

Re: macro or array

Ksharp — Fri, 06 Jun 2014 11:46:54 GMT

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