topic Re: Calculate cumulative cases by age and ID in SAS Programming

Calculate cumulative cases by age and ID

ANKH1 — Wed, 23 Aug 2023 22:35:20 GMT

This is sample of a dataset in the long format:

ID	age
1	131
1	0
1	0
2	22
2	76
3	0
3	48
3	48
3	3

We then ran the following to get the frequencies of every ID by age

proc freq data=dsin;

table ID*age;

run;

And the output looks like:

	age
ID	0	3	22	48	76	131
1	2	0	0	0	0	1
2	0	0	1	0	1	0
3	1	1	0	2	0	0

But now we also need to get the cumulative number of people who had an incident up until every age. The tricky part is that once a person has an incident and it is accounted for, if this person has incidents after that, it doesn't contribute to increasing the count anymore. Let's say for ID3 it presents an incident at age 0, 3 and 48. But this person can only contributes to the count at age 0. And also, if they have more than one incident at the same age, it counts as 1. The output should look like this:

Age	Cumulative
0	2
3	2
22	3
48	3
76	3
131	3

Re: Calculate cumulative cases by age and ID

ballardw — Wed, 23 Aug 2023 22:55:22 GMT

I can guess that "had an incident" means every age recorded is an incident. What I can't decipher is what "up until every age" or " it is accounted for" means.

"Let's say for ID3 it presents an incident at age 0, 3 and 48. But this person can only contributes to the count at age 0." WHY? Exactly what in the data tells us to stop considering at 0?

Re: Calculate cumulative cases by age and ID

ANKH1 — Wed, 23 Aug 2023 23:05:56 GMT

So at each age you sum the IDs that have values not equal to zero. Values 1,2,3 at each age means, that at that age an incident was reported. We want to know at each age how many IDs have accumulated first incidents. But if an ID reports incidents at more than one age, this ID will be accounted when their first incident is reported.

Re: Calculate cumulative cases by age and ID

andreas_lds — Thu, 24 Aug 2023 07:29:17 GMT

I am not sure, that is understand your request completely. The following steps seem to create what you expect. In future questions please post data in usable form.

data have;
   input ID age;
   datalines;
1 131
1 0
1 0
2 22
2 76
3 0
3 48
3 48
3 3
;

/* Get lowest age for each id */
proc sql;
   create table work.selection as
      select distinct Id, Age
         from work.have
         group by Id
         having Age = min(Age)
         order by Age
      ;
quit;

/* Get all ages */
proc sort data= work.have(keep= Age) out= work.ages nodupkey;
   by Age;
run;

/* Combine and count */
data want;
   merge work.ages work.selection(keep= Age in= inc);
   by Age;

   if inc then Cumulative + 1;

   if last.Age then output;
run;

Re: Calculate cumulative cases by age and ID

Ksharp — Thu, 24 Aug 2023 11:42:17 GMT

data have;
infile cards truncover expandtabs;
value=1;
input ID	age;
cards;
1	131
1	0
1	0
2	22
2	76
3	0
3	48
3	48
3	3
;
proc sort data=have out=temp nodupkey;
by age id;
run;
proc transpose data=temp out=temp2(drop=_name_) prefix=_;
by age;
id id;
var value;
run;
data temp2;
 set temp2;
 id=1;
run;
data temp3(drop=id);
 update temp2(obs=0) temp2;
 by id;
 output;
run;
data want;
 set temp3;
 Cumulative=sum(of _:);
run;

Re: Calculate cumulative cases by age and ID

Tom — Thu, 24 Aug 2023 13:38:14 GMT

So you seem to want to count the subjects at their first incident age. So first reduce the data to just the minimum age per subject.

data have;
   input ID age;
datalines;
1 131
1 0
1 0
2 22
2 76
3 0
3 48
3 48
3 3
;

proc means data=have ;
  by id ;
  var age ;
  output out=cases(drop=_freq_ _type_ id) min=;
run;

proc sort data=cases ;
  by age;
run;

You seem to want to include all of the other age points in your report so get that from the original data also and then combine the two.


proc sort data=have(keep=age) out=age_points nodupkey;
  by age;
run;

data want;
  merge cases(in=incases) age_points;
  by age;
  cummulative + incases;
  if last.age;
run;

Result

Obs    age    cummulative

 1       0         2
 2       3         2
 3      22         3
 4      48         3
 5      76         3
 6     131         3

Re: Calculate cumulative cases by age and ID

ANKH1 — Thu, 24 Aug 2023 13:58:23 GMT

Thank you! I will post data in usable form in the future.