topic How to easily import when excel workbook changes in SAS Enterprise Guide

How to easily import when excel workbook changes

mrdlau — Tue, 22 May 2018 21:37:26 GMT

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?

Re: How to easily import when excel workbook changes

tomrvincent — Wed, 06 Jun 2018 15:50:04 GMT

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,

Re: How to easily import when excel workbook changes

mrdlau — Tue, 12 Jun 2018 03:21:07 GMT

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?

Re: How to easily import when excel workbook changes

SASKiwi — Tue, 12 Jun 2018 07:54:52 GMT

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.

Re: How to easily import when excel workbook changes

tomrvincent — Wed, 13 Jun 2018 12:09:12 GMT

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