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.
\\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"
\\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"
Okay, I apologize. It's a csv file, not an xlsx file. Really my bad. End of day and I wasn't careful. Sorry.
@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.
If CSV and multiple files you can avoid a macro and read all at once similar to the approach in this thread.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.