How to check whether account number in Excel spreadsheet are in SAS dataset

jeremy4 — Mon, 16 Mar 2020 12:43:47 GMT

Hi,

I have an Excel spreadsheet (titled "Account number query"), who contains a list of approximately 500 account numbers (variable is called "ACCOUNT_NUMBER"). The Excel spreadsheet can be found in the following location for me (if the Excel spreadsheet needs to be imported):

G:\2. Projects\2020\Account number query.xlsm

I have been asked whether these 500 account numbers on the Excel spreadsheet are in the SAS dataset (ac.account_information), which has over a million observations and also contains the same "ACCOUNT_NUMBER" variable.

Please can someone help with the code required to answer the following two questions?

Question 1

Would it be possible to show which account numbers are in both the Excel spreadsheet (titled "Account number query") and the SAS dataset (ac.account_information), and which account numbers are in the Excel spreadsheet but not the SAS dataset? Does the Excel spreadsheet information have to be imported to create a new SAS dataset, then compare it with the SAS dataset (ac.account_information)?

Question 2

Where the "ACCOUNT_NUMBER" is the same in both the Excel spreadsheet and the SAS dataset, would it be possible to merge on just the "DATE_OPEN" and "BALANCE" fields (and not "MONTH_END") from the Excel spreadsheet (see screenshot below) onto the SAS dataset for the matching "ACCOUNT_NUMBER" observations?

Information

In the SAS dataset (ac.account_information), the "ACCOUNT_NUMBER" variable is a character type, with a $30. format and informat of $30.

A example of the Excel spradsheet (Account number query) is shown below:

Re: How to check whether account number in Excel spreadsheet are in SAS dataset

PaigeMiller — Mon, 16 Mar 2020 12:35:13 GMT

First you import the Excel file into a SAS data set. Then you can use PROC SQL joins to answer both of your questions.

topic Re: How to check whether account number in Excel spreadsheet are in SAS dataset in SAS Programming

How to check whether account number in Excel spreadsheet are in SAS dataset

Re: How to check whether account number in Excel spreadsheet are in SAS dataset