How to easily import when excel workbook changes

[mrdlau]

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?

[tomrvincent]

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]

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]

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]

&path has to refer to the path and file you're importing. The format depends on your OS.