Finding if the Accounts are available in the datasets

[Sandeep77]

Hello Experts,

I have a datasets which has a lot of information. I have imported an excel file (Nov_BF) which has just 43 account numbers (Account_ID). I want to see if these 43 account number are available in the Finrep_sorted dataset or not. I would also like to add all the variables from Finrep_sorted for these 43 accounts from Nov_BF. Here is the sample dataset:

Data Finrep_sorted;
infile cards expandtabs;
input ACCOUNT_ID FINREP_CURR_SEGMENT$ FINREP_CURR_SEGMENT_REASON$ SOURCE_SYSTEM_CD$ MONTH;
datalines ;
1000032 PERFORMING MEETREQUIREMENTS AF 202311
1000040 PERFORMING MEETREQUIREMENTS AF 202311
1000278 PERFORMING MEETREQUIREMENTS AF 202311
1000307 PERFORMING MEETREQUIREMENTS AF 202311
5464782 PERFORMINGFORBORNE PROBATIONNOTENDED AF 202311
5320998 NON-PERFORMING CONTAGION AF 202311
;
run;
I want the output data to show all the matching accounts and all the variables from Finrep_sorted. I tried merging them and then find but my code gives an error. Please guide.

Data merged_data;
Merge Nov_BF (In=InNov_BF)
Finrep_sorted (In=InFinrep_sorted);
by ACCOUNT_ID;
If InFinrep_sorted; /* Filter to include only matching account numbers */
run;
Error message:
28         Data merged_data;
29         Merge Nov_BF (In=InNov_BF)
30         Finrep_sorted (In=InFinrep_sorted);
31         by ACCOUNT_ID;
32         If InFinrep_sorted; /* Filter to include only matching account numbers */
33         run;

ERROR: BY variable ACCOUNT_ID is not on input data set WORK.NOV_BF.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.MERGED_DATA may be incomplete.  When this step was stopped there were 0 observations and 6 variables.
WARNING: Data set WORK.MERGED_DATA was not replaced because this step was stopped.

[PaigeMiller]

You get this error, which seems to be very self-explanatory.

ERROR: BY variable ACCOUNT_ID is not on input data set WORK.NOV_BF.

You can't merge by a variable that doesn't exist in one (or both) of the data sets.

[Sandeep77]

But the excel file that I imported (Nov_BF) does have Account_ID and from the sample dataset you can see that there is Account_ID in the Finrep_sorted as well. Then why it is showing that there is no account_id?

[PaigeMiller · Accepted solution]

My rule: when SAS says something is true and the user says something is not true, then I believe SAS. Show us PROC CONTENTS output on the SAS data set WORK.NOV_BF. It also does NOT matter what was in Excel; what matters is what is in SAS.

(In fact, whenever you find yourself in this situation where it appears you don't have a specific variable in that data set, you need to check the PROC CONTENTS output for that data set)