Hello - I would like to get your opinions / suggestions on how to best creat specific logic in SAS. I have the following heirachy table in SAS. I am currently creating reports for TOH_AGENCY's only (based on their code) and I now need to incorporate the LEVEL_DOWN 2 codes into the reporting but not where LEVEL_DOWN 2 agency code equals TOH_AGENCY. Then I need to incorporate the LEVEL 3 into the reporting but not where LEVEL 3 agency code equals LEVEL_DOWN 2 or TOH_AGENCY. Lastly, I need to incorporate reports for AOR_CODE, where AOR_CODE is not equal to TOH_AGENCY or LEVEL_DOWN 2 or LEVEL 3.
I would appreciate any suggestions you have. Thank you!!
TOH_AGENCY | LEVEL_DOWN 2 | LEVEL 3 | AOR_CODE |
5700 | 5700 | 5700 | |
5724 | 5724 | 5724 | |
K002 | K002 | K002 | |
K002 | K017 | K017 | |
K002 | K018 | K018 | |
K002 | K023 | K023 | |
K002 | K031 | K031 | |
K002 | K032 | K032 | |
K002 | K062 | K062 | |
K002 | K077 | K077 | |
K002 | K088 | K088 | |
K002 | K116 | K116 | |
K002 | K150 | K150 | |
K002 | K311 | K311 | |
K002 | K337 | K337 | |
K002 | K355 | K355 | |
K002 | K356 | K356 | |
K002 | K367 | K367 | |
K002 | K380 | K380 | |
K002 | K381 | K381 | |
K002 | K411 | K411 | |
K004 | K004 | K004 | |
K004 | K008 | K008 | |
K004 | K008 | K008 | K040 |
K004 | K008 | K008 | K042 |
K004 | K008 | K008 | K069 |
K004 | K008 | K008 | K143 |
K004 | K008 | K008 | K145 |
K004 | K008 | K008 | K169 |
K004 | K008 | K008 | K184 |
K004 | K008 | K008 | K200 |
K004 | K008 | K008 | K241 |
K004 | K008 | K008 | K358 |
K004 | K008 | K008 | K378 |
K004 | K010 | K010 | |
K004 | K011 | K011 | |
K004 | K011 | K011 | K275 |
@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;
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.