topic Re: import xlsx file using macros about years in SAS Programming

import xlsx file using macros about years

geneshackman — Thu, 04 Aug 2022 20:36:41 GMT

Hi all. I'm hoping this is a fairly simple question, I can't get the syntax quite right. Or else I'm making a simple typo that I just can't see.

%let start=18; *first year;
%let end=%eval(&start+2); *end year;
%let start4=2018;
%let end4 = %eval(&start4+2);

proc import out = pzip&start.&end
datafile = "\\dohfile02\phig\PHIGDATA\Data\perinatal_zips\perinzip_&start4._&end4.xlsx"
DBMS=XLSX REPLACE;
getnames = yes;
SHEET="PerinZIP_&start4._&end4";
options MSGLEVEL=i;
run;

The exel file is named:

PerinZIP_2018_2020.xlsx

and the sheet I want is:

PerinZIP_2018_2020

In this folder

\\dohfile02\phig\PHIGDATA\Data\perinatal_zips

the error i get is generally this, "Physical file does not exist"

316 proc import out = pzip&start.&end
317 datafile =
317! "\\dohfile02\phig\PHIGDATA\Data\perinatal_zips\perinzip_&start4._&end4.xlsx"
318 DBMS=XLSX REPLACE;
319 getnames = yes;
320 SHEET="PerinZIP_&start4._&end4";
321 options MSGLEVEL=i;
322 run;

ERROR: Physical file does not exist,
\\dohfile02\phig\PHIGDATA\Data\perinatal_zips\perinzip_2018_2020xlsx.
NOTE: The SAS System stopped processing this step because of errors.

Re: import xlsx file using macros about years

Reeza — Thu, 04 Aug 2022 20:39:23 GMT

\\dohfile02\phig\PHIGDATA\Data\perinatal_zips\perinzip_2018_2020xlsx

Macro processor ate the period. You need to have double periods for the file extension.

datafile = "\\dohfile02\phig\PHIGDATA\Data\perinatal_zips\perinzip_&start4._&end4..xlsx"

Re: import xlsx file using macros about years

geneshackman — Thu, 04 Aug 2022 20:45:59 GMT

Thanks Reeza. I tried that and still got an error. I must have a typo that I just can't find. 😞

153 proc import out = pzip&start.&end
154 datafile =
154! "\\dohfile02\phig\PHIGDATA\Data\perinatal_zips\perinzip_&start4._&end4..xlsx"
155 DBMS=XLSX REPLACE;
156 getnames = yes;
157 SHEET="PerinZIP_&start4._&end4";
158 options MSGLEVEL=i;
159 run;

ERROR: Physical file does not exist,
\\dohfile02\phig\PHIGDATA\Data\perinatal_zips\perinzip_2018_2020.

Re: import xlsx file using macros about years

geneshackman — Thu, 04 Aug 2022 20:52:36 GMT

Okay, I apologize. It's a csv file, not an xlsx file. Really my bad. End of day and I wasn't careful. Sorry.

Re: import xlsx file using macros about years

Reeza — Thu, 04 Aug 2022 20:54:16 GMT

If you're on Unix file paths are case sensitive.
If you're using Studio, go to Servers Folders and Files. Navigate to the path and right click the file. Copy the path exactly from properties and paste that into your program. If using EG or Foundation, the same thing can be done in the Explorer window.

If Windows go to the file, hold down CTRL+SHIFT+Right Click and Copy Path to get the exact path.

Re: import xlsx file using macros about years

ballardw — Thu, 04 Aug 2022 21:03:32 GMT

@geneshackman wrote:

Okay, I apologize. It's a csv file, not an xlsx file. Really my bad. End of day and I wasn't careful. Sorry.

SAS was taking the easy way out on you. The DBMS = would have thrown an error if the file is actually CSV not XLSX.

The SHEET= option would thrown an error as CSV files cannot have sheets.

If all of these files that are name similarly mean that they are supposed to have the same content you might be much better off writing data step code to read them so all of the variables have the same properties. Proc Import on multiple similar files quite often means individual data sets end up with different properties such as variable length and sometimes type due to contents as the Procedure has to guess separately for each file what the properties are.

As a minimum you want to set a large value for the GUESSINGROWS option if you use Proc Import with CSV so that more of the file is examined before properties for variables are set.

Re: import xlsx file using macros about years

Reeza — Thu, 04 Aug 2022 21:14:08 GMT

If CSV and multiple files you can avoid a macro and read all at once similar to the approach in this thread.

https://communities.sas.com/t5/SAS-Programming/Retrieve-and-clean-all-files-in-a-directory-one-by-one/m-p/827202