I have a table like below, there are only 4 distinct IDs but have 6 observations. I'd like to count how many people are in type 1 only, in type 2 only, in type 3 only, and in Combination of 3 types...
Is there a code capable of fulfilling this process?
ID |
Type | Length |
1 | 1 | 108 |
1 | 2 | 54 |
1 | 3 | 87 |
2 | 1 | 46 |
3 | 1 | 93 |
4 | 2 |
29 |
I guess what you want is to make a profile of each ID (7 profiles possibles: 3 single types, 3 pairs, and 1 triple), and then do a frequency of those profiles. But then what do you want to do about the LENGTH variable? Is it to be aggregated? averaged? ignored? Or do you simply want a frequency of profiles?
The code below does nothing but report the frequency of each type-profile:
data have;
input ID Type Length;
datalines;
1 1 108
1 2 54
1 3 87
2 1 46
3 1 93
4 2 29
run;
data profiles (keep=id type1-type3);
do until (last.id);
set have;
by id;
array dummy {3} type1-type3;
dummy{type}=1;
if first.id then put id= ;
end;
do _n_=1 to dim(dummy);
if dummy{_n_}=. then dummy{_n_}=0;
end;
run;
proc freq data=profiles;
table type1*type2*type3 / list;
run;
But if you are interested in corresponding statistics about other vars, like LENGTH, you have to describe what you want.
BTW: this code assumes that your data are sorted by ID.
Alternatively, you can use COUNT(DISTINCT ...) in PROC SQL:
proc sql;
create table want as
select comb, count(distinct id) as n from
(select id, compress(cat(max(type=1),2*max(type=2),3*max(type=3)),'0') as comb
from have
group by id)
group by comb
order by input(comb,3.);
quit;
data have;
input id type length;
cards;
1 1 108
1 2 54
1 3 87
2 1 46
3 1 93
4 2 29
;
proc transpose data=have out=have_t;
by id;
var type;
run;
data want;
set have_t;
length category $ 10;
call sortn(of col:);
category=compress(cats(of col:),'.');
run;
Hello @LarissaW
I see you what are looking for is "I'd like to count how many people are in type 1 only, in type 2 only, in type 3 only, and in Combination of 3 types..."
The following code create two tables Types for the first case and combinations for the second case.
The table Types answers the question how many users there in a are given type?
The table Combination answers the question in how many type's is a user present.?
data have;
input id type length;
cards;
1 1 108
1 2 54
1 3 87
2 1 46
3 1 93
4 2 29
;
proc sql;
create table Type as
Select TYPE,Count(ID) as users from have
group by Type
order by Type;
Create table combination as
Select ID, Count(Type) as TYPES from have
group by ID
order by ID;
quit;
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.