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!
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.