BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
ANKH1
Pyrite | Level 9

Hi, there is the following dataset where each ageX column indicates if there was an incident or not.

data dsin;
input ID age1 age2 age3 age4 age5 age6;
datalines;
1 1 0 0 1 0 0
2 0 1 1 0 0 0
3 0 0 0 1 0 0
4 0 0 0 0 0 0
5 1 0 0 0 1 0
6 0 0 1 0 0 0
;
run;

We would like to have the cumulative number of people who had an incident by 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 still counts only as 1. Let's say for ID1:it presents an incident at age1, and age4 as well. But this person can only be accounted once. The below table is the output we are looking for:

Ageage1age2age3age4age5age6
Cumulative234555
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
data dsin;
input ID age1 age2 age3 age4 age5 age6;
datalines;
1 1 0 0 1 0 0
2 0 1 1 0 0 0
3 0 0 0 1 0 0
4 0 0 0 0 0 0
5 1 0 0 0 1 0
6 0 0 1 0 0 0
;
run;

data _dsin / view=_dsin;
set dsin;
array age age1-age6;
flag=0;
do i=1 to dim(age);
if age(i) >= 1 and flag=0 then do; age(i)=1; flag=1; end
else if age(i) >= 1 and flag=1 then age(i) = 0;
end;
keep id age:;
run;

proc means data=_dsin noprint;
output out=agg1 sum=;
run;

data want;
set agg1;
array age age1-age6;

do i=2 to dim(age);
age(i) = age(i) + age(i-1);
end;
keep age:;
run;

That modification should work 🙂

View solution in original post

8 REPLIES 8
Reeza
Super User

Should be a faster way than this, but I'm sleep deprived today.

 

data dsin;
input ID age1 age2 age3 age4 age5 age6;
datalines;
1 1 0 0 1 0 0
2 0 1 1 0 0 0
3 0 0 0 1 0 0
4 0 0 0 0 0 0
5 1 0 0 0 1 0
6 0 0 1 0 0 0
;
run;

data _dsin / view=_dsin;
set dsin;
array age age1-age6;
flag=0;
do i=1 to dim(age);
if age(i) = 1 and flag=0 then flag=1;
else if age(i) = 1 and flag=1 then age(i) = 0;
end;
keep id age:;
run;

proc means data=_dsin noprint;
output out=agg1 sum=;
run;

data want;
set agg1;
array age age1-age6;

do i=2 to dim(age);
age(i) = age(i) + age(i-1);
end;
keep age:;
run;

ANKH1
Pyrite | Level 9

Hi! Thank you so much for your response. It worked perfectly. But I encountered that some of the datasets will have more than 1 incident for the same ID in the same age variable. Is it possible to account for this in the code? That is, the ID will only be accounted once, even though it reported 2 or more incidents for the same ID. 

Reeza
Super User
That would imply a different structure than above as you have only 1 variable per age, or that it's not just 0/1?
ANKH1
Pyrite | Level 9
Yes, these numbers 0,1,2 come from a frequency table.
Reeza
Super User
data dsin;
input ID age1 age2 age3 age4 age5 age6;
datalines;
1 1 0 0 1 0 0
2 0 1 1 0 0 0
3 0 0 0 1 0 0
4 0 0 0 0 0 0
5 1 0 0 0 1 0
6 0 0 1 0 0 0
;
run;

data _dsin / view=_dsin;
set dsin;
array age age1-age6;
flag=0;
do i=1 to dim(age);
if age(i) >= 1 and flag=0 then do; age(i)=1; flag=1; end
else if age(i) >= 1 and flag=1 then age(i) = 0;
end;
keep id age:;
run;

proc means data=_dsin noprint;
output out=agg1 sum=;
run;

data want;
set agg1;
array age age1-age6;

do i=2 to dim(age);
age(i) = age(i) + age(i-1);
end;
keep age:;
run;

That modification should work 🙂

Ksharp
Super User
data dsin;
input ID age1 age2 age3 age4 age5 age6;
datalines;
1 1 0 0 1 0 0
2 0 1 1 0 0 0
3 0 0 0 1 0 0
4 0 0 0 0 0 0
5 1 0 0 0 1 0
6 0 0 1 0 0 0
;
run;

proc sql;
create table want as
select 'Cumulative' as Age ,
 sum(age1) as age1,
 sum(max(age1,age2)) as age2,
 sum(max(age1,age2,age3)) as age3,
 sum(max(age1,age2,age3,age4)) as age4,
 sum(max(age1,age2,age3,age4,age5)) as age5,
 sum(max(age1,age2,age3,age4,age5,age6)) as age6
 from dsin ;
quit;
mkeintz
PROC Star
data dsin;
input ID age1 age2 age3 age4 age5 age6;
datalines;
1 1 0 0 1 0 0
2 0 1 1 0 0 0
3 0 0 0 1 0 0
4 0 0 0 0 0 0
5 1 0 0 0 1 0
6 0 0 1 0 0 0
run;

data want (keep=cumcount:);
  set dsin end=end_of_data;
  array age {6};
  array cumcount {6} (6*0);

  if whichn(1,of age{*})>0 then do _n_=whichn(1,of age{*}) to dim(age);
    cumcount{_n_}+1;
  end;

  if end_of_data;
run;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Tom
Super User Tom
Super User

It probably would be simpler not to have that wide structure to start with.

data have;
  input ID @;
  do age=1 to 6;
     input incident  @;
     Cumulative=max(incident ,Cumulative);
     output;
  end;
datalines;
1 1 0 0 1 0 0
2 0 1 1 0 0 0
3 0 0 0 1 0 0
4 0 0 0 0 0 0
5 1 0 0 0 1 0
6 0 0 1 0 0 0
;

Then getting the sum is easy.

proc means nway sum;
  class age;
  var cumulative;
run;

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 8 replies
  • 644 views
  • 0 likes
  • 5 in conversation