topic Re: Automate excel files import to SAS in SAS Programming

Automate excel files import to SAS

mayasak — Tue, 21 Jun 2022 07:48:28 GMT

Hi,

I'm working on a project that needs to import data from 2 websites to MS Access on a daily basis. The data sets are exported from Access to excel and then merged into 4 datasets. The files also will be exported from Excel to SAS, cleaned, deduplicated, matched, and merged into one data set then exported back to excel.

My question - Is there any code that I can use to automate data import to SAS/ export to excel and/or any part of the data manipulation.

Thank you

Re: Automate excel files import to SAS

Patrick — Tue, 21 Jun 2022 08:52:05 GMT

From MS Access to MS Excel to SAS and then back to Excel? Feels like a very convoluted process. Excel is not a very good data source because it doesn't provide "stable" column definitions.
My "gut feel" is: This design requires re-assessment.

Questions

1. Is SAS running on the same platform (computer) than Access or Excel?

2. Do you have SAS Access to PC Files licensed

3. IF SAS runs on the same platform than Access: Would it be an option for SAS to directly read from MS Access? Or would it even be an option for SAS to pull the information directly from the website?

You will need to provide more information how your environment looks like for us to really provide guidance.

Re: Automate excel files import to SAS

Kurt_Bremser — Tue, 21 Jun 2022 09:56:02 GMT

You use three tools where one should suffice. Do everything in SAS.

Are the websites public?

Re: Automate excel files import to SAS

mayasak — Tue, 21 Jun 2022 18:02:22 GMT

Hi Patrick,

Thank you Patrick for the quick response. Here are my answers to your questions.

Is SAS running on the same platform (computer) than Access or Excel? Yes, it is running on the same computer.

2. Do you have SAS Access to PC Files licensed. Yes, I have SAS Access to PC files licensed

For this, I would definitely prefer the process in the following order:

SAS to pull the information directly from the website but I need guidance on how to do that, if possible!
SAS to pull data from MS Access, but I have to do cleaning, deduplicating and merging the data in SAS.
Use Excel between SAS and Access and do the cleaning and deduplication in Excel then import the data in SAS.

Thank you

Re: Automate excel files import to SAS

mayasak — Tue, 21 Jun 2022 18:22:08 GMT

Hi Kurt,

The websites are not public. I have no clue if it's possible to pull the data from the websites to SAS.

The first one needs my org email and a password to access. When I get the report from this website, I only have one option, MS Access to export the data.
The second one needs to access through a third-party user name and password and then unlock an identity code token. This website has SAS option for the export but I need to generate a "new data set" step to update the data before export.

Thank you

Re: Automate excel files import to SAS

Kurt_Bremser — Tue, 21 Jun 2022 18:26:07 GMT

So the "pull" is a mostly manual process, and you get a .accdb file from the first, and could get a .sas7bdat from the second?

Re: Automate excel files import to SAS

mayasak — Tue, 21 Jun 2022 19:13:10 GMT

Hi Kurt,

Yes, that's what I think is the case. So most probably the automation should be done after this step, if possible.

Thank you

Re: Automate excel files import to SAS

Kurt_Bremser — Tue, 21 Jun 2022 19:17:01 GMT

With SAS/ACCESS to PC Files, you can read the .accdb file directly, and a .sas7bdat file can immediately be used, so there's no need to involve Excel at all.

Re: Automate excel files import to SAS

mayasak — Tue, 21 Jun 2022 19:54:13 GMT

So, can the import from Access to SAS be automated? That is when new data are added to Access, it will be reflected automatically in SAS. The other issue is that I do a lot of data manipulation in excel, and by skipping this, I have to do all the work in SAS. Is there a way to automate this step (data cleaning, deduplicating, and merging) in SAS?

Thank you

Re: Automate excel files import to SAS

Patrick — Wed, 22 Jun 2022 09:52:16 GMT

If you can operationalize your data cleansing process then using SAS will allow for much better control and auditability than using MS Access or even worse MS Excel.

Accessing data from the Internet using SAS is possible. If and how exactly this needs to work technically "depends". If this is a public URL then I'd suggest you ask a separate question, provide the URL and show exactly what data you want to pull into SAS.

Data cleansing using SAS is "standard".

Creating Excel files using SAS is "standard".

Using SAS will allow you to "automate" the process as long as you're able to pre-define the cleansing rules.

I believe with SAS Viya/VA there are also some interactive ways for data cleansing - but given what you shared that's not the environment you're operating in.

Re: Automate excel files import to SAS

Kurt_Bremser — Thu, 23 Jun 2022 07:23:48 GMT

Any SAS code can be automated. What you need is a reliable trigger that tells the scheduling mechanism that new data is available on the website.