@Reeza I'm not sure if this helps, if you have any suggestions I apreciate it.
Code below: there is a variable called "aor_code" that contains all agency codes used for reporting. In the query below a Case Statement is used to break down the "aor_code" into different levels; TOH_AGENCY, LEVEL_DOWN 2, Level 3. I am currently creating reports for all agency's listed in the TOH_AGENCY list by using a where staement to filter for only those. I'm trying to determine the best way to create logic to incorporate reporting for Level 2's, then Level 3. I suppose I could create another egp file, and filter for only levels 2's in a where statement. that seems a bit cumberson to me but I think it would work.
proc sql;
create table Reporting3 as
select distinct
a.agency_name,
a.aor_code,
a.agency_type,
a.'Level 3'n as 'LEVEL 3'n,
case
when a.'Level 2'n is null then a.aor_code
else a.'Level 2'n end as 'LEVEL_DOWN 2'n,
case
when a.TOH_agency is null then a.aor_code
else a.TOH_agency end as 'TOH_AGENCY'n,
compbl(strip(cat(propcase(a.agents_name)," ",propcase(a.agents_last_name)))) as AGENT_NAME,
a.role,
a.agent_individual_writing_number,
a.license,
a.agent_npn,
a.agent_email_address,
a.agent_work_phone_number,
propcase(a.cty_nm) as CTY_NM,
a.usps_stt_cd,
a.zip_code,
a.county,
a.rts_goals_per_county,
a.broker_managers,
a.assigned_broker_manager,
a.cms_pass_fail,
a.cms_cert_eff_date,
a.cms_cert_term_date,
b.SITE_LICENSES as AHIP_ON_FILE,
b.ahip_status,
a.course_title,
a.certification_status,
case when role = "Agent" and max(year(CMS_CERT_TERM_DATE)) ne 2022 and b.SITE_LICENSES is missing then 1 else 0
end as CHECK_AHIP,
case when role = "Agent" and max(year(CMS_CERT_TERM_DATE)) ne 2022 and b.SITE_LICENSES is not missing then 1 else 0
end as TRAIN_INDICATOR,
case when role = "Agent" and max(year(CMS_CERT_TERM_DATE)) ne 2022 and b.SITE_LICENSES is missing then 'AHIP NEEDED' /*else 'AHIP OnFile'*/
end as CHECK_AHIP_2 length=25,
case when role = "Agent" and max(year(CMS_CERT_TERM_DATE)) ne 2022 and b.SITE_LICENSES is not missing then 'READY TO TRAIN' /*else 'Not RTT'*/
end as TRAIN_INDICATOR_2
from Reporting2 a
left join cpm1p1.ahip_dmp b
on a.agent_npn = b.npn
where a.aor_code not in ('K001','K003')
where
(a.aor_code in ('5700','5724','S001','S006','S009','S010','S011','S012','S013','S014','S015','S016','S017','S018','S019','S020','S021','S022','S023','S024','S025','S026', 'S027')
or a.aor_code like ('K%')
or a.aor_code like ('k%'))
group by
a.agent_npn
order by a.aor_code asc;
quit;
