I'm having difficulty with a pretty easy procedure. I'm trying to get the number of records based on groups (O, IV and Combination of O+IV).
I have the following data.
data have; infile datalines delimiter=","; input ID, recordkey, route $; datalines; 1,1,IV 1,2,IV 1,3,IV 2,4,O 2,5,IV 2,6,IV 3,7,O 3,8,O ;
How would I get a count of unique IDs that are O alone, IV alone and combination of O and IV?
I want to get the following results:
Oral=1
IV=1
Combo=1
data have;
infile datalines delimiter=",";
input ID recordkey route $;
datalines;
1,1,IV
1,2,IV
1,3,IV
2,4,O
2,5,IV
2,6,IV
3,7,O
3,8,O
;
proc sql;
create table want as
select 'Oral' as name,count(distinct id) as n from have group by id having sum(route='O')=count(*)
union
select 'IV' as name,count(distinct id) from have group by id having sum(route='IV')=count(*)
union
select 'Combo' as name,count(distinct id) from have group by id having count(distinct route)>1;
quit;
do these
3,7,O 3,8,0
belong to O?
And count of distinct ids for IV seems 2 , are you sure it's IV=3????
Sorry, I updated the error in the initial code on the last line.
Hi @tm28 a very interesting problem. Certainly not that simple for me as it is probably for you
data have;
infile datalines delimiter=",";
input ID recordkey route $;
datalines;
1,1,IV
1,2,IV
1,3,IV
2,4,O
2,5,IV
2,6,IV
3,7,O
3,8,O
;
proc sql;
create table want as
select grp, count(distinct id) as count
from
(select *,ifc(count(distinct route) =1 ,route,'combo') as grp from have group id)
group by grp;
quit;
This works great for the most part, except for O route.. It should have a count of 1, not 2.
@tm28 Here is my test:
data have;
infile datalines delimiter=",";
input ID recordkey route $;
datalines;
1,1,IV
1,2,IV
1,3,IV
2,4,O
2,5,IV
2,6,IV
3,7,O
3,8,O
;
proc sql;
create table want as
select grp, count(distinct id) as count
from
(select *,ifc(count(distinct route) =1 ,route,'combo') as grp from have group id)
group by grp;
quit;
proc print noobs;run;
grp | count |
---|---|
IV | 1 |
O | 1 |
combo | 1 |
data have;
infile datalines delimiter=",";
input ID recordkey route $;
datalines;
1,1,IV
1,2,IV
1,3,IV
2,4,O
2,5,IV
2,6,IV
3,7,O
3,8,O
;
proc sql;
create table want as
select 'Oral' as name,count(distinct id) as n from have group by id having sum(route='O')=count(*)
union
select 'IV' as name,count(distinct id) from have group by id having sum(route='IV')=count(*)
union
select 'Combo' as name,count(distinct id) from have group by id having count(distinct route)>1;
quit;
This got to what I wanted (minus the union part of the code), thanks so much!
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.