BookmarkSubscribeRSS Feed
mrdlau
Obsidian | Level 7
In my workspace, i have a shortcut to an excel workbook with multiple sheets. If i right click > import, im able to export my worksheets out. However, if i ever need to change workbooks that ends up having a different number of columns, i need to ‘modify import’ in order to make sure all the appropriate columns are accounted for.

Im working with many different sheets that may or may not have differring columns. Is there a way to run the process flow where it will always just re-import?
4 REPLIES 4
tomrvincent
Rhodochrosite | Level 12

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,

mrdlau
Obsidian | Level 7

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?

SASKiwi
PROC Star

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.

tomrvincent
Rhodochrosite | Level 12
&path has to refer to the path and file you're importing. The format depends on your OS.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 907 views
  • 0 likes
  • 3 in conversation