Desktop productivity for business analysts and programmers

How to easily import when excel workbook changes

Reply
Contributor
Posts: 43

How to easily import when excel workbook changes

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?
Regular Contributor
Posts: 180

Re: How to easily import when excel workbook changes

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,

Contributor
Posts: 43

Re: How to easily import when excel workbook changes

Posted in reply to tomrvincent

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?

Super User
Posts: 3,856

Re: How to easily import when excel workbook changes

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.

Regular Contributor
Posts: 180

Re: How to easily import when excel workbook changes

&path has to refer to the path and file you're importing. The format depends on your OS.
Ask a Question
Discussion stats
  • 4 replies
  • 203 views
  • 0 likes
  • 3 in conversation