topic Re: Getting a successful proc import in SAS Programming

Getting a successful proc import

wlierman — Tue, 03 Nov 2020 22:03:53 GMT

This is a common question regarding using Proc Import to import xlsx and csv files into SAS.

I have used this code for the last couple imports and it worked well. It is shone here

Proc import datafile = "I:\Health Analytics\AMB_INFORMATICS\ARIAS_COUNTY_RACE_ETHNICITY\All Contacts_Added_Fields 11_3_2020.xlsx"
            out = SASCDC_2.ARIAS_County_Race_Ethnicity
            dbms = xlsx	
			replace;
			getnames=yes;
			run;

But the error of Physical file does not exist comes back. I have probably walked through the coding a dozen times.

Proc import datafile = "I:\Health Analytics\AMB_INFORMATICS\ARIAS_COUNTY_RACE_ETHNICITY\All
501! Contacts_Added_Fields 11_3_2020.xlsx"
502 out = SASCDC_2.ARIAS_County_Race_Ethnicity
503 dbms = xlsx
504 replace;
505 getnames=yes;
506 run;

ERROR: Physical file does not exist, I:\Health
Analytics\AMB_INFORMATICS\ARIAS_COUNTY_RACE_ETHNICITY\All Contacts_Added_Fields 11_3_2020.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

Is there anything that I am forgetting. Someone suggested removing some of the file steps and just have the xlsx / csv files after the AMB_INFORMATICS step. The thing is though this coding worked with the last import about two weeks ago.

Any help or direction will be appreciated.

Thanks.

wlierman

Re: Getting a successful proc import

SASKiwi — Tue, 03 Nov 2020 22:12:33 GMT

I'm sure this is stating the obvious but have you checked that "I:\Health Analytics\AMB_INFORMATICS\ARIAS_COUNTY_RACE_ETHNICITY\All Contacts_Added_Fields 11_3_2020.xlsx" still exists?

Re: Getting a successful proc import

Reeza — Tue, 03 Nov 2020 22:19:15 GMT

Given that the date of the file is today, that's a new file so there's no guarantee the file is there. Go to the actual file in Windows, hold down CTRL+SHIFT and right click the file. Select Copy as Path. Then use that in your code to avoid any issues.

Re: Getting a successful proc import

ballardw — Tue, 03 Nov 2020 22:29:07 GMT

If a person is naming these files, or code creating them may change, you might have an issue with multiple spaces in the actual file path.

When you look at something it may be hard to catch the extra space between:

"I:\Health Analytics\AMB_INFORMATICS\ARIAS_COUNTY_RACE_ETHNICITY\All Contacts_Added_Fields 11_3_2020.xlsx"

and

"I:\Health Analytics\AMB_INFORMATICS\ARIAS_COUNTY_RACE_ETHNICITY\All Contacts_Added_Fields  11_3_2020.xlsx"

Worse could be use of a character other than the typical blank that appears as a space

"I:\Health Analytics\AMB_INFORMATICS\ARIAS_COUNTY_RACE_ETHNICITY\All Contacts_Added_Fields 11_3_2020.xlsx"

In the line immediately above the "space" before the 11 is actually an ASCII-255 , not a space.

Suggestion: If you can talk to the people creating these if the names are in YYYYMMDD format, and use 2 digits for both month and day, then at least the files will sort properly using OS tools. I.E:

"I:\Health Analytics\AMB_INFORMATICS\ARIAS_COUNTY_RACE_ETHNICITY\All Contacts_Added_Fields 20201103.xlsx"

Re: Getting a successful proc import

wlierman — Wed, 04 Nov 2020 00:01:00 GMT

Thank you for the procedure to check. I have not used it before and look forward to applying it in this situation.

wlierman