Array vs macro for calculating mean duration of time experiencing an event

[ginak]

Hello,

My data set has the following variables:

event1 t1a0 t1a1 t1a2 .... t1a18 event2 t2a0 t2a1 t2a2 t2a3 .... t2a18  ......... event20 t20a0 t20a1 t20a2 t20a3 ..... t20a18

So each variable is binary. the child either did or did not receive event 1 (1=yes, 0=no), ..., the child either did or did not receive event 20 (1=yes, 0=no).

the child either did or did not receive event1 at age0 (t1a0=1 if yes, t1a0=0 if no),

the child either did or did not receive event1 at age1 (t1a1=1 if yes, t1a1=0 if no),

.

.

.

the child either did or did not receive event1 at age18 (t1a18=1 if yes, t1a18=0 if no),

.

.

.

the child either did or did not receive event20 at age0 (t20a0=1 if yes, t20a0=0 if no),

the child either did or did not receive event20 at age1 (t20a1=1 if yes, t20a1=0 if no),

.

.

.

the child either did or did not receive event20 at age18 (t20a18=1 if yes, t20a18=0 if no),

My question is this - I would like to present the mean duration of years the child experienced each of the 20 events and the standard deviation, as well as the number of kids who experienced each type of event and the percentage of those who did so and put together in a nice table as such:

Header 1	Header 2	Header 3
event type	endorsed: % (n)	duration: mean (SD)
event 1	55.44 (2550)	1.89 (1.94)
event 2	etc.	etc
......	.....	...
event 20	etc	etc

I was able to calculate the % and n endorsed for each event. However, I'm not sure how to calculate the mean duration of years that each child experienced the event. For example, the child may have had experienced event 1 at ages 0, 1, 5, 6, 16, 17, 18 and event 5 at ages 0, 5, 6, 7. Each child can experience multiple events.

I was trying to do an array but I don't think my way is correct...

Given that the names of the variables are similar (t[event #]a[age]), how could I calculate this?

For event 2 i could calculate the # of years each child experienced the event as

duration2= sum(of t2:);

But now I want to do this for each event. I dont' want to make 20 arrays b/c there are 20 events, and in my array statement I'd have 20*18=360 variables which is too long.. any suggestions as to how I could calculate the mean duration of each event and the standard deviation? I can clarify if this is too confusing..

And then what, in your opinion is the best way to present this in a table? I'd like to just ods output it, or use ods trace to obtain values to put into a proc report.

Thanks,

Gina

[andreas_lds · Accepted solution]

Not easy to suggest something without a sample dataset.

Just a starter, important note: event numbers must start with leading zeros

data test;

    length kid 8

        Event01 Event02 Event03 $ 10

        t01a1-t01a10 t02a1-t02a10 t03a1-t03a10 8;

    drop i;

    array t01 t01:;

    array t02 t02:;

    array t03 t03:;

    Event01 = 'one';

    Event02 = 'two';

    Event03 = 'three';

    do kid = 1 to 4;

        do i = 1 to dim(t01);

            t01 = mod(int(ranuni(0) * 415547409), 2);

            t02 = mod(int(ranuni(0) * 345345), 2);

            t03 = mod(int(ranuni(0) * 94532), 2);

        end;

        output;

    end;

run;

%macro bob;

proc sql noprint;

     select substr(Name, 5) into :VarList separated by ' '

     from sashelp.vcolumn

     where libname = 'WORK' and memname = 'TEST' and Name like 'Event%';

quit;

/* VarList contains t01 t02 t03 ... */

data work.NextStep;

    set work.test;

    keep kid event: duration_t:;

/* the loop creates one sum-function for each event; */

    %do i = 1 %to %sysfunc(countw(&VarList));

        %let var = %scan(&VarList, &i, %str( ));

        duration_&var = sum(of &var:);

    %end;  

run;

%mend;

%bob;

Now transpose work.NextStep, so that it contains one observation for each kid and event. Afterwards required statistics can be calculated by the usual route (proc means, maybe tabulate).

[ginak]

thank you!

[Ksharp]

Not sure whether it is what you need.

data test;
input event1 t1a0-t1a18;
cards;
1 0 0 1 1 0 1 0 1 1 1 1 1 0 0 0 0 1 1 1
;
run;

data temp;
 set test;
array x{*} _all_;
length _name $ 40;
do i=1 to dim(x);
_name=vname(x{i});
value=x{i};
output;
end;
run;
data temp;
 set temp;
 length name $ 40;
 retain name;
 if _name eq: 'event' then do; name=_name;delete;end;
proc sql;
create table want as
 select name, catx(' ',sum(value=1)/count(*),'(', count(*),')') as endorse,catx(' ',mean(value),'(',std(value),')') as mean_std
  from temp
    group by name;
quit;

Ksharp

[ginak]

Thank you, you helped me a lot I am a bit of a "newbie" when it comes to SAS and so my programming skills aren't that advanced.