topic Re: question to drop certain variables in SAS Programming

question to drop certain variables

LMSSAS — Mon, 24 Jan 2022 13:17:51 GMT

I am running the below code and need to drop certain variables that fall under the column name"pstn.sals_aget_cd as AgentCode". I am aware of the Drop function, what I am trying to do is drop any varaibles that start with 6,T,or W. they are character varibale and in the data look like the following: W001-1806, 6909-003, T005-015. So I need to drop any AgentCode that startes with 6,T,or W. Can someone provide me with suggestions on best way to accomplish this

proc sql;
create table Truli_Agents as
select distinct
lic.SALS_AGET_LIC_ID as License
,icp.GIVN_NM as FirstName
,icp.SRNM_NM as LastName
,icp.NATL_PDCR_NB as NPNNumber
,appt.AGET_APPT_STAT_TYP_CD
,appt.SALS_APPT_CD as AppointmentCode
,pstn.sals_aget_cd as AgentCode
,RLE.AGEY_INDV_CHNL_PTNR_ROLE_CD as Position_Role
,nme.sals_agey_nm as AgencyName
,appt.SALS_AGET_APPT_BGN_DT as ApptStartDate
,appt.SALS_AGET_APPT_END_DT as ApptEndDate
,RLE.SALS_AGEY_PSTN_ROLE_SAR_DT AS Agent_Pos_Start_Dt
,RLE.SALS_AGEY_PSTN_ROLE_END_DT AS Agent_Pos_End_Dt
,Propcase(PTAL.CTY_NM) AS City 
,PTAL.USPS_STT_CD AS State
,SUBSTR (PTAL.PTAL_CD,1,5) AS Postal_Code
 /*,MAX(PTAL.AD_EFCV_DT) as Postal_Eff_Dt format=date9.*/
,(PTAL.AD_EFCV_DT) as Postal_Eff_Dt format=date9.

from cpm1p1.indv_chnl_ptnr icp
INNER JOIN cpm1p1.SALS_AGEY_pstn pstn on icp.indv_chnl_ptnr_id = pstn.indv_chnl_ptnr_id and icp.TENANT_ID = pstn.TENANT_ID
inner join cpm1p1.SALS_AGEY_pstn_role rle on icp.indv_chnl_ptnr_id = rle.indv_chnl_ptnr_id and icp.TENANT_ID = rle.TENANT_ID and pstn.sals_agey_id = rle.sals_agey_id
left outer join cpm1p1.SALS_AGET_LIC lic on icp.INDV_CHNL_PTNR_ID = lic.INDV_CHNL_PTNR_ID and icp.TENANT_ID = lic.TENANT_ID
left outer join cpm1p1.SALS_AGET_APPT appt on icp.INDV_CHNL_PTNR_ID = appt.INDV_CHNL_PTNR_ID and icp.TENANT_ID = appt.TENANT_ID 
left outer join cpm1p1.INDV_CHNL_PTNR_PTAL_AD ptal on icp.INDV_CHNL_PTNR_ID = ptal.INDV_CHNL_PTNR_ID 
/*left outer join cpm1p1.SALS_AGEY sa on sa.SALS_AGEY_ID = rle.SALS_AGEY_ID and sa.TENANT_ID = rle.TENANT_ID*/
left outer join cpm1p1.SALS_AGEY_NM nme on pstn.SALS_AGEY_ID = nme.SALS_AGEY_ID and pstn.TENANT_ID = nme.TENANT_ID


where 
/*RLE.SALS_AGEY_PSTN_ROLE_END_DT >= today()*/
/*RLE.SALS_AGEY_PSTN_ROLE_SAR_DT >= appt.SALS_AGET_APPT_BGN_DT*/
/*and RLE.SALS_AGEY_PSTN_ROLE_END_DT <= today()*/
lic.SALS_AGET_LIC_ID IS NOT NULL
and (RLE.SALS_AGEY_PSTN_ROLE_END_DT ge appt.SALS_AGET_APPT_BGN_DT)
and RLE.AGEY_INDV_CHNL_PTNR_ROLE_CD = 'Agent'
and appt.SALS_APPT_CD in ('BHF')
and pstn.sals_aget_cd IS NOT NULL
/*and PTAL.USPS_STT_CD in ('FL')*/
/*and lic.SALS_AGET_LIC_ID in ('A035017')*/

Group By
lic.SALS_AGET_LIC_ID
,icp.GIVN_NM
,icp.SRNM_NM
,icp.NATL_PDCR_NB
/*,appt.AGET_APPT_STAT_TYP_CD*/
,nme.SALS_AGEY_NM
,rle.SALS_AGEY_pstn_role_SAR_DT
,rle.SALS_AGEY_PSTN_ROLE_END_DT
,appt.SALS_APPT_CD
,appt.SALS_AGET_APPT_BGN_DT
,appt.SALS_AGET_APPT_END_DT
,appt.AGET_APPT_STAT_TYP_CD
,RLE.SALS_AGEY_PSTN_ROLE_END_DT

having Postal_Eff_Dt=max(Postal_Eff_Dt)

ORDER BY
lic.sals_aget_lic_id ASC,
appt.SALS_AGET_APPT_END_DT DESC
;
quit;

Re: question to drop certain variables

AMSAS — Mon, 24 Jan 2022 13:27:46 GMT

I assuming by drop, you mean deleting any observation where AgentCode that startes with 6,T,or W (My definition of drop is dropping a variable, which doesn't make sense to me in your question).

If you mean deleting any observation where AgentCode that startes with 6,T,or W then this works:

data have ;
	infile cards ;
	input agentCode $ ;
cards ;
A123
B456
C789
;

proc sql ;
	select *
	from have 
	where (substr(agentCode,1,1) not in ("A","C")) ;
quit ;

Re: question to drop certain variables

LMSSAS — Mon, 24 Jan 2022 13:37:59 GMT

Yes, you are correct. I mean delete.
Will I need to list each observation of AgentCode that I want to delete? because there are a lot of them all different. It looks like under
cards ; you are listed each observation out.
A123
B456
C789

Re: question to drop certain variables

AMSAS — Mon, 24 Jan 2022 18:52:02 GMT

I'm not sure I understand your question. The first data step is just some code to generate some simple sample data, I've changed it below to fit with your question better.
The key part that you are interested in is the where statement, which uses SUBSTR function to determine the 1st character and only keeps those that are not "T", "W" or "6"

data have ;
	infile cards ;
	input agentCode $10. ;
cards ;
A123
W-DeleteMe
B456
T-DeleteMe
C789
;

proc sql ;
	create table want as
	select *
	from have 
	where (substr(agentCode,1,1) not in ("W","T","6")) ;
quit ;

Re: question to drop certain variables

LMSSAS — Mon, 24 Jan 2022 18:58:31 GMT

I appreciate your clarifying, I was reading your original response incorrectly..
Thank you, The proc sql did work.
Thank you for your time today