I have two following datasets
demographics
group | ID | age | gender |
A | 123 | 54 | Female |
A | 456 | 32 | Female |
A | 656 | 35 | Male |
B | 145 | 45 | Female |
B | 444 | 47 | Male |
events
ID | term | serious |
123 | fever | 1 |
123 | chills | 0 |
444 | heart attack | 1 |
What I would like to do is show the data by GROUP in demographics and total by group, total record ID from events and total 'serious' from events
want:
group | total | total_events | total_serious |
A | 3 | 2 | 1 |
B | 2 | 1 | 1 |
Here is what i have so far
proc sql;
select distinct a.group, count(a.ID) as total_participants, count(b.ID) as total_events
from demographics a
left join events b on a.ID = b.ID
group by a.group;
quit;
i am able to get the total participants and total events but not serious
See this:
data demo;
input group $ ID $ age gender $;
datalines;
A 123 54 Female
A 456 32 Female
A 656 35 Male
B 145 45 Female
B 444 47 Male
;
data events;
infile datalines dsd;
input ID $ term :$20. serious;
datalines;
123,fever,1
123,chills,0
444,heart attack,1
;
proc sql;
create table want as
select
a.group,
count(distinct a.id) as total,
count(b.term) as events,
sum(b.serious) as serious
from demo a left join events b
on a.id = b.id
group by a.group
;
quit;
proc print data=want noobs;
run;
Result:
group total events serious A 3 2 1 B 2 1 1
See this:
data demo;
input group $ ID $ age gender $;
datalines;
A 123 54 Female
A 456 32 Female
A 656 35 Male
B 145 45 Female
B 444 47 Male
;
data events;
infile datalines dsd;
input ID $ term :$20. serious;
datalines;
123,fever,1
123,chills,0
444,heart attack,1
;
proc sql;
create table want as
select
a.group,
count(distinct a.id) as total,
count(b.term) as events,
sum(b.serious) as serious
from demo a left join events b
on a.id = b.id
group by a.group
;
quit;
proc print data=want noobs;
run;
Result:
group total events serious A 3 2 1 B 2 1 1
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.