This is what I do:
PROC IMPORT OUT= outdataset
DATAFILE= "&pathfile"
DBMS=&ext REPLACE;
SHEET="&sheet";
GETNAMES=no;
RUN;
&ext would be xls or xlsx.
This will bring in all the columns (as text) that have data and name them A, B, C, D,...
You can then rename and convert them as you please. You can even use the names in the 1st row, if that's where they are.
Here's an example of how to convert Excel date into SAS date:
input(Date_Received,best10.)-21916 as Received_Dt format date9.,
And a currency amount:
input(Provider_Billed_Amount,dollar12.2) as Provider_Billed_Amt format dollar12.2,
thanks
when I do this, I get a 'physical file not found'.
does the path need to be in a specific format? I read that if I'm running SAS on a virtual machine, it has to be done differently?
I depends where your SAS code is running. Does your Enterprise Guide connect to a remote SAS server or a local one? In either case the folder the spreadsheet is stored in must be readable by your SAS server.
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.