Hello, I have a dataset that looks like this: data Have;
input ID callDate :ddmmyy. cat1 cat2 cat3 cat4;
format callDate ddmmyy10.;
datalines;
1 15/02/2021 0 0 0 0
1 17/02/2021 0 0 0 0
1 19/02/2021 0 0 1 0
2 08/02/2021 0 0 0 1
2 09/02/2021 1 0 0 0
3 10/02/2021 0 0 0 0
3 11/02/2021 1 0 0 0
3 15/02/2021 1 0 0 0
4 01/02/2021 0 0 0 0
4 02/02/2021 0 0 0 0
4 03/02/2021 0 0 0 0
4 04/02/2021 0 1 0 0
4 05/02/2021 0 0 0 0
4 23/02/2021 0 1 0 0
5 10/02/2021 0 0 0 0
;
run; I just grabbed a snippet of february's data but there is data for every month, and more data for each month. Basically, a client ID can be contacted many times a month and in each call they can be put in a category. My objective is to find out how many UNIQUE ID's entered each category each month. To do this, I need to follow a hierarchy: cat1>cat2>cat3>cat4>no category. So if a client appears in both category 1 and 3 in a month, they should only be counted for category 1, not 3. Here is how the output should look: data Have;
input yearmonth :mmyys7. cat1 cat2 cat3 cat4 nocat;
format yearmonth mmyys7.;
datalines;
Feb-21 2 1 1 0 1
;
run; As you can see: -ID 1 was counted towards Cat 3 -ID 2 was counted towards Cat 1 only, not 4, because cat1>cat4 -ID 3 was counted towards Cat 1 once -ID 4 was counted towards Cat 2 once -ID 5 was counted towards nocat I believe this is not very complicated but what I've done so far has not been working: proc sql;
create table test as
select count(distinct id) as unique_id
, month(calldate) as month
, count ( case when cat1=1 then 1 else 0 end) as cat1
from have
group by month;
quit;
proc sql;
create table test2 as
select count(distinct id) as unique_id
, month(calldate) as month
, count (distinct case when cat2=1 then 1 else 0 end) as cat2
from have
group by month;
quit;
proc sql;
create table test3 as
select count(distinct id) as unique_id
, month(calldate) as month
, count (distinct case when cat3=1 then 1 else 0 end) as cat3
from have
group by month;
quit;
proc sql;
create table test4 as
select count(distinct id) as unique_id
, month(calldate) as month
, count (distinct case when cat4=1 then 1 else 0 end) as cat4
from have
group by month;
quit; I couldn't make it work with year-month either. If you could show me a way to solve it in PROC SQL it is very much appreciated, since the rest of my code is all in that format.
... View more
