You seem to want to  1) Filter by some selection criteria 2) Count how many stays (across any clinic) per client 3) Count number of clients per clinic and how many of those clients had multiple stays.   So you want something like: data subset; set have; where clinic_type=3 and year=2023 and r=1 and cost>0 ; run; proc sql; create table stays_per_client as select *,client_id,count(*) as n_stays from subset group by client_id ; create table want as select clinic , count(*) as client_count , sum(n_stays>1) as clients , mean(n_stays>1) as pct format=percent. from stays_per_client group by clinic ; quit; Which results in this report: So you could collapse that into a single SQL query by nesting it. proc sql; create table want as select clinic , count(*) as client_count , sum(n_stays>1) as clients , mean(n_stays>1) as pct format=percent. from ( select clinic,client_id,count(*) as n_stays from have where clinic_type=3 and year=2023 and r=1 and cost>0 group by client_id ) group by clinic ; quit; Note that this query is taking advantage of the fact that SAS will remerge summary statistics back onto the detail observations when you do something like: select id,var2,mean(x) as mean_x from have group by id If you want to run this SQL in some other system's implementation of SQL you will have to explicitly to the merge (join in SQL speak) yourself.  Make sure to apply the same WHERE clause to both sub queries.  And perhaps replace the boolean expressions with CASE clauses. proc sql; create table want as select a.clinic , count(*) as client_count , sum(case when n_stays>1 then 1 else 0 end) as clients , mean(case when n_stays>1 then 1 else 0 end) as pct format=percent. from (select clinic,client_id from have where clinic_type=3 and year=2023 and r=1 and cost>0 ) a inner join (select client_id,count(*) as n_stays from have where clinic_type=3 and year=2023 and r=1 and cost>0 group by client_id ) b on a.client_id=b.client_id group by a.clinic ; quit;   ... View more

Below is untested, but why not change     and LDOS lt Span_end_3 and FDOS lt Span_end_3 to and (span_end_3=. or LDOS lt Span_end_3 ) and (span_end_3=. or FDOS lt Span_end_3 ) Editted note: If you have instances of both span_end_3 and LDOS (or FDOS) the above suggestion would still pass the IF test.  If you want such situations to fail the test, then use     and LDOS lt max(LDOS,Span_end_3) and FDOS lt max(FDOS,Span_end_3)     This will force both LDOS (or FDOS) and span_end_3 to be non-missing as well as satisfy the desired inequality.   ... View more

I think the code(wrote by me) you posted has nothing to do with your question. Data Have; input ID$ Clinic_Name_1$ Clinic_Name_2$ Clinic_Name_3$ Prior_Span1$ Prior_Span2$ Prior_Span3$ Cost service$ ; datalines; 1 B A . 0 0 0 168 A 2 A A . 1 0 0 46 B 2 A A . 0 1 0 99 C 3 B . . 0 0 0 24 D 4 D . . 0 0 0 54 A 5 B D F 0 1 0 405 B 7 A . . 1 0 0 90 F 8 E E A 0 0 1 87 J 9 F . . 0 0 0 0 G 10 C C C 0 0 0 40 C 10 C C C 0 0 1 374 B 10 C C C 0 1 1 60 A 15 A B C 1 1 1 60 A ; run; proc sql; create table classdata as select * from ( select distinct Clinic_Name_1 from have where Clinic_Name_1 is not missing union select distinct Clinic_Name_2 from have where Clinic_Name_1 is not missing union select distinct Clinic_Name_3 from have where Clinic_Name_1 is not missing ), (select distinct service from have where service is not missing) ; create table want as select Clinic_Name_1,service,count(*) as count,sum(cost) as cost from have where Prior_Span1='1' group by Clinic_Name_1,service union all select Clinic_Name_2,service,count(*) as count,sum(cost) as cost from have where Prior_Span2='1' group by Clinic_Name_2,service union all select Clinic_Name_3,service,count(*) as count,sum(cost) as cost from have where Prior_Span3='1' group by Clinic_Name_3,service ; quit; proc tabulate data=want classdata=classdata; class service Clinic_Name_1; var count cost; table service all,(Clinic_Name_1='Clinic' all)*(count*f=f20.0 cost*f=dollar22.0); keylabel sum=' ' all='Total'; run;   ... View more

Looks like you want a report, not a data set.  Consider something like this:   proc format; picture mypct low-high=' 009.9% '; run; proc tabulate data=have noseps; class clinic gender ; var cost; table clinic=' ' * gender=' ' , N='Count'*f=5.0 sum=' '*cost='Cost'*f=dollar13.0 cost=' '*pctsum<gender>='%'*f=mypct. /Box="Clinic Gender" rts=20; run; I guess the percents that you want are percent cost for a given gender within a given clinic.     ... View more