I have to exclude certain accounts from saving a file.

[Sandeep77]

Hi Experts,

I have created a table with all the information. Now before saving the data set, I want to exclude certain accounts from it. I have imported those accounts in SAS. I know I can use the where statement and exclude them but not sure how should I code it. Can you please assist? This is the code I am using for saving the data set. There are two files I have imported, 1. Result_for_Overseas and 2. Result_for_Ret. I want to exclude all the accounts in these two files. Thanks

data store.final_file ; 
set work.final_file; 
run; 

[PaigeMiller]

data store.final_file ; 
set work.final_file; 
where account not in ('1234567','238Ty45');
run; 

[Sandeep77]

Thank you for your reply.

Just to confirm in the below comment should I enter the file name instead of '1234567','238Ty45' or I have to open the file and use all the account numbers like below?

where account not in ('1234567','238Ty45');

[Sandeep77]

Hi Expert,
Can I do it like this?

data store.final_file ;
set work.final_file;
where Accountnumber not in work.'results for overseas'n and
Accountnumber not in work.'results for ret'n;
run;

[Tom]

---

@Sandeep77 wrote:

Hi Expert,
Can I do it like this?

data store.final_file ;
set work.final_file;
where Accountnumber not in work.'results for overseas'n and
Accountnumber not in work.'results for ret'n;
run;

---

No.

You could use MERGE to test if they accounts are in the list. (PS do not use such goofy names for your datasets, as you can see it just makes trying to use them unnecessarily difficult with out any added benifits).

data store.final_file ;
  merge work.final_file(in=in1)
        work.results_for_overseas(keep=accountnumber in=in2)
        work.results_for_ret(keep=accountnumber in=in3)
  ;
  by accountnumber;
  if in1 and not in2 and not in3;
run;

[Sandeep77]

Hi, I am getting the below error message:

35         Data store.final_file;
36         merge work.final_file(in=in1)
37         work.'results for overseas'n(keep=Accountnumber in=in2)
38         work.'results for ret'n(keep=Accountnumber in=in3);
39         by Accountnumber ;
40         if in1 and not in2 and not in3;
41         run;

ERROR: BY variables are not properly sorted on data set WORK.'RESULTS FOR RET'n.

[PaigeMiller]

You have to sort this data set by accountnumber before your code will work.

From now on, please show us the ENTIRE log for a PROC or DATA step that has an ERROR, rather than showing us a part of the log.

[Sandeep77]

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Saving final File';
4          %LET _CLIENTPROCESSFLOWNAME='Importing stoplist checked';
5          %LET _CLIENTPROJECTPATH='S:\Trace\Trace Integrity Analyst Data\BAU_Processes\SAS\Enhanced TPT\V8 Original Address
5        ! Combined Outputs.egp';
6          %LET _CLIENTPROJECTPATHHOST='LWLT5CG9322XFL';
7          %LET _CLIENTPROJECTNAME='V8 Original Address Combined Outputs.egp';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=SVG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         %macro HTML5AccessibleGraphSupported;
15             %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16         %mend;
17         ODS LISTING GPATH=&sasworklocation;
18         FILENAME EGHTML TEMP;
19         ODS HTML5(ID=EGHTML) FILE=EGHTML
20             OPTIONS(BITMAP_MODE='INLINE')
21             %HTML5AccessibleGraphSupported
22             ENCODING='utf-8'
23             STYLE=HTMLBlue
24             NOGTITLE
25             NOGFOOTNOTE
26             GPATH=&sasworklocation
27         ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
28         
29         /*
30         data store.final_file ;
31         set work.final_file;
32         run;
33         */
34         
35         Data store.final_file;
36         merge work.final_file(in=in1)
37         work.'results for overseasn(keep=accountnumberin=in2)
38         work.'results for ret'n(keep=accountnumberin=in3);
39         by accountnumber;
40         if in1 and not in2 and not in3;
41         run;

ERROR: BY variables are not properly sorted on data set WORK.'RESULTS FOR RET'n.
in1=0 in2=0 in3=1 accountnumber=168450062 icustomerid=. Sent Title=  Sent Forename=  Sent Surname=  Sent DOB=. Sent Address line 1= 
Sent Address line 2=  Sent Address line 3=  Sent Address line 4=  Sent Address line 5=  Sent Post code=  SEQUENCE_NUMBER=.
Reference_Number=  ADDRESS_MATCHED=  ERROR_MESSAGE=  SEGMENT_NUMBER=  SEGMENT=  OPS_FLAG=  TA_ADDRESS1=  TA_ADDRESS2=  TA_ADDRESS3= 
TA_ADDRESS4=  TA_PCODE=  RANK_1=. RANK_TYPE_1=  APPENDED_DOB=. APPENDED_FORENAME=  NO_INDIVIDUALS_SHARING_NM_DOB=.
NO_INDIVIDUALS_SHARING_NM_NO_DOB=. DECEASED=  LAS_RES_IND=  LAS_NON_RES_IND=  LAS_NO_OPEN_INSIGHT_ACC=.
LAS_NO_OPEN_INSIGHT_ACC_L3M=. LAS_NO_OPEN_INSIGHT_ACC_LM=. LAS_NO_DIFF_OPEN_INSIGHT_LENDERS=. LAS_CURRENTLY_REGISTERED_ON_ER= 
LAS_TIME_SINCE_SUBJECT_ON_EER=. LAS_NEW_RR_IN_DIFF_SURNAME=  LAS_TIME_SINCE_RR_DELETE_FOR_APP=  LAS_REGISTERED_TELEPHONE_NUMBER= 
LAS_TEL_NUMBER=  LAS_COUNT_ACTIVITY_IN_L1M=  LAS_COUNT_ACTIVITY_IN_L3M=  MOVEMENT_OUT_INDICATOR=  LAS_RES_COUNT_INCL_MOV_INFO=.
LAS_NON_RES_COUNT_INCL_MOV_INFO=. TA_RESIDENCY_IND=  TA_NON_RESIDENCY_IND=  TA_NO_OPEN_INSIGHT_ACC=. TA_NO_OPEN_INSIGHT_ACC_L3M=.
TA_NO_OPEN_INSIGHT_ACC_L1M=. TA_NO_DIFF_OPEN_INSIGHT_LENDERS=. TA_CURR_REG_ON_ER=  TA_TIME_SINCE_SUBJECT_ON_EER=.
TA_NEW_RR_IN_DIFF_SURNAME=  TA_TIME_SINCE_RR_DELETE_FOR_APP=  TA_REG_TEL_NUM=  TA_TEL_NUM_1=  TA_COUNT_ACTIVITY_IN_L1M= 
TA_COUNT_ACTIVITY_IN_L3M=  TA_RES_COUNT_INCL_MOV_INFO=. TA_NON_RES_COUNT_INCL_MOV_INFO=. TYPE_1=  LINK_DATE_1=.
EQUIFAX_ADDRESS_KEY_1=  STD_CODE_1=  LOCAL_NUMBER_1=  TPS_1=  DATE_LOADED_1=. LINE_TYPE_1=  DISTANCE_1=. NAME_MATCH_1=  NAME_1= 
2                                                          The SAS System                         09:06 Thursday, September 29, 2022

DOB_1=. ADDITIONAL_NAME1_1=  ADDITIONAL_NAME2_1=  ADDITIONAL_NAME3_1=  DISPUTE_ALERT=  CIFAS=  CSISF30=. OPS_NUM_S=. OPS_NUM_T=.
EQ_SL_Address1=  EQ_SL_Address2=  EQ_SL_Address3=  EQ_SL_Address4=  EQ_SL_Postcode=  EQ_SL_On Stop List=  EQ_SL_Returned= 
EQ_SL_Gone Away=  EQ_SL_Invalid Address=  Eq_add_conflict=  no_acc_EQ_con=. EQ_cust_conflict=  ClientId=  SequenceNo=. CIG_URN=.
DataDNA=. Name_CT=  Title_CT=  Forename_CT=  Othername_CT=  Surname_CT=  POBox=  Abode=  BName=  BNumber=  Street1=  Street2= 
Sublocality=  Locality=  Town=  Postcode_CT=  OnER=. CreditActive=  AddressLink=  LatestConfirmationDate=. AddressType= 
MatchLevel=  OccupancyStatus=  Occupier=  TheOccupierFirstDate=  TheOccupierLastDate=  JointName=  NameInAddress=  Deceased1= 
DeceasedConfidence=. DateOfSale=. SalePrice=. PropertyType=  Tenure=  AveDetachedPropValue=. AveSemiDetachedPropValue=.
AveTerracedPropValue=. AveFlatPropValue=. TelephoneNo=  ExDir=  P2PScore=. DOB_CT=. BAIOrderType=  BAIRestrictionType= 
BAIOrderStatus=  BAIOrderNumber=  BAIOrderDate=. BAICourt=  JudgementActiveCount=. JudgementTotalAmount=. LatestJudgementStatus= 
LatestJudgementDate=. LatestJudgementAmount=. NUM_PORTF=. AccountType=  DOBMatch=  DOBMultipleMatches=  InputDOB=. Distance=.
Cohabiting=  ResidenceCount=. TransiencyCount=. TransiencyBand=  TransiencyIndex=. InTouchLandline=. InTouchLandlineRecencyDate=.
InTouchMobile=. InTouchMobileRecencyDate=. InTouchEmail=  InTouchEmailRecencyDate=. InTouchOccupation=  InTouchEmploymentStatus= 
InTouchDateLastActiveAtAddress=  JobNo=  DateRun=. CallTrace_Segment_Result=  Active_Bankrupcty=  P2CScore=. TU_SL_Address1= 
TU_SL_Address2=  TU_SL_Address3=  TU_SL_Address4=  TU_SL_Postcode=  TU_SL_On Stop List=  TU_SL_Returned=  TU_SL_Gone Away= 
TU_SL_Invalid Address=  TU_add_conflict=  no_acc_TU_con=. TU_cust_conflict=  TU_outcome=  EQ_outcome=  result_Comparison= 
EQ_segment_number=  TU_pos_rank1=. EQ_pos_rank1=. TU_rank_2=. EQ_rank_2=. TU_final_rank=. EQ_final_rank=. Best_rank=. Result_CRA= 
Chosen_result_outcome=  Selected_segment=  Traced_ad_1=  Traced_ad_2=  Traced_ad_3=  Traced_ad_4=  Traced_postcode= 
Lettered_at_PC_flag=. lettered_flag=. NOA_sent_flag=. NOA_sent_pc_flag=. CL_Lettered_PC=. CL_lettered=. CL_NOA_sent=. CL_NOA_PC=.
rep_code=  client_code=  dt_curbal=. sector=  Current_Add1=  Current_Postcode=  Result_to_current_ad=  non_uk_PC=. BookOnDate=.
rundate=. Days_since_bookon=. years_since_bookon=. bookon_12m_ago=  LFL_LAS_or_NEW=  FIRST.accountnumber=1 LAST.accountnumber=1 _ERROR_=1
_N_=3
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 1 observations read from the data set WORK.FINAL_FILE.
NOTE: There were 1 observations read from the data set WORK.'RESULTS FOR OVERSEAS'n.
NOTE: There were 4 observations read from the data set WORK.'RESULTS FOR RET'n.
WARNING: The data set STORE.FINAL_FILE may be incomplete.  When this step was stopped there were 0 observations and 230 variables.
WARNING: Data set STORE.FINAL_FILE was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.07 seconds
      user cpu time       0.00 seconds
      system cpu time     0.03 seconds
      memory              2387.18k
      OS Memory           35700.00k
      Timestamp           09/29/2022 02:32:47 PM
      Step Count                        86  Switch Count  0
      

42         
43         
44         %LET _CLIENTTASKLABEL=;
45         %LET _CLIENTPROCESSFLOWNAME=;
46         %LET _CLIENTPROJECTPATH=;
47         %LET _CLIENTPROJECTPATHHOST=;
48         %LET _CLIENTPROJECTNAME=;
49         %LET _SASPROGRAMFILE=;
50         %LET _SASPROGRAMFILEHOST=;
51         
52         ;*';*";*/;quit;run;
53         ODS _ALL_ CLOSE;
54         
55         
56         QUIT; RUN;
57       

Ok I am sharing the full log below. Also, how do I sort the accountnumber. Do I need to write proc sort code or short in excel before importing? I am sorry, I am not good with SAS and learning how it works.

[Tom]

PROC SORT is the way to sort.

Sort each dataset independently.  Then you can merge them.