Logic Question

[LMSSAS]

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]

Depends on how the reporting was incorporating the codes....unfortunately the question is too vague to comment at this point.

[LMSSAS]

@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;