topic Re: Duplicate ID question in SAS Programming

Duplicate ID question

LarissaW — Tue, 20 Feb 2024 14:17:14 GMT

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

Re: Duplicate ID question

mkeintz — Tue, 20 Feb 2024 15:02:06 GMT

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.

Re: Duplicate ID question

FreelanceReinh — Tue, 20 Feb 2024 15:05:17 GMT

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;

Re: Duplicate ID question

PaigeMiller — Tue, 20 Feb 2024 18:16:28 GMT

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;

Re: Duplicate ID question

Sajid01 — Tue, 20 Feb 2024 17:44:08 GMT

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;