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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.