Help using Base SAS procedures

Using DDE to open and close xlsX - disable compatibility pop up

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Using DDE to open and close xlsX - disable compatibility pop up

Hi, Wrote some code that opens and xlsx and saves as xls as our version of sas doesn't support "new" versions of Excel.

Only issue I have is that a pop up appears asking user to confirm compatibility issues.

Is there a way to suppress this so the code can run without user having to click a button?

Thanks

Andy


Accepted Solutions
Solution
‎06-12-2015 09:20 AM
Community Manager
Posts: 2,954

Re: Using DDE to open and close xlsX - disable compatibility pop up

Posted in reply to andrewjmdata

I can sympathize.  We can't always control the format of the data feeds we consume.  We have a couple of sayings in the US related to being a small cog in a large machine. One is about "the tail wagging the dog".  And the second is "some days you're the bug, and some days you're the windshield."

View solution in original post


All Replies
Super User
Super User
Posts: 7,962

Re: Using DDE to open and close xlsX - disable compatibility pop up

Posted in reply to andrewjmdata

Hi,

No, that would be an Excel "feature".  I would recommend not using DDE, it is very old now and doesn't support any functionality since early Excel versions.  Is there a reason you cannot write out to tagset.excelxp?  That creates XML which Excel then interprets, gives most functionality.  Also, I always recommend using a tool other than Excel anyways.  Whatever task it is your doing will be far better suited using tools designed for the task.

Occasional Contributor
Posts: 6

Re: Using DDE to open and close xlsX - disable compatibility pop up

Hi Thanks for the reply, I'll look into tagset.excelp, can you suggest an good info sources?

My aim is to get xlsx files into sas. The data is sent as xlsx, I have no control of it.

I take your point on the excel "feature". A pain.

Super User
Super User
Posts: 7,962

Re: Using DDE to open and close xlsX - disable compatibility pop up

Posted in reply to andrewjmdata

Now I am a bit confused, are you just reading the file in?  If so then convert the file to CSV and use a datastep import - full control that way.  Proc import which has been suggested is just a guessing tool, you will have problems.  If the data is sent as XLSX, then that is your real problem.  Any proper data user will not send data as XLSX, seriously if that is their only pplication, use someone else, but even then, saving the file as CSV rather than XLSX would be preferable.  ODBC is an option, the thing you will face, and this is the case with anything in relation to Excel is that Excel is *not* a database or data transfer format.  I.e. the data is unstructured.   Each cell has its own format/attributes, this view doesn't correlate to data processing/basing techinques.  You can of course work around this slightly by having and import document which states columns/datatypes etc.  Then if the file doesn't match them its not your problem.

Occasional Contributor
Posts: 6

Re: Using DDE to open and close xlsX - disable compatibility pop up

I have to work with what I have ie xlsx. Saving to CSV misses the point. I want the whole process to be as seamless as possible.

Am pretty sure the ODBC route will work, that should get me data straight into SAS.

Thanks

Solution
‎06-12-2015 09:20 AM
Community Manager
Posts: 2,954

Re: Using DDE to open and close xlsX - disable compatibility pop up

Posted in reply to andrewjmdata

I can sympathize.  We can't always control the format of the data feeds we consume.  We have a couple of sayings in the US related to being a small cog in a large machine. One is about "the tail wagging the dog".  And the second is "some days you're the bug, and some days you're the windshield."

Occasional Contributor
Posts: 6

Re: Using DDE to open and close xlsX - disable compatibility pop up

Posted in reply to ChrisHemedinger

This works perfectly... (http://support.sas.com/kb/32/455.html)

libname boo oledb init_string="Provider=Microsoft.ACE.OLEDB.12.0; data source=C:\excelfiles\air.xlsx; extended Properties=Excel 12.0";


data new;

     set boo.'air3'n;

run;


Thanks folks!

Community Manager
Posts: 2,954

Re: Using DDE to open and close xlsX - disable compatibility pop up

Posted in reply to andrewjmdata

If you have SAS/ACCESS to PC Files, look at these techniques:

  • PROC IMPORT with DBMS=XLSX (SAS 9.3 and later)
  • LIBNAME XLSX to read your XLSX file just like a collection of data sets (SAS 9.4)

If you have an older version of SAS that doesn't support XLSX directly, you could try using ODBC as described in this SAS note.  This requires SAS/ACCESS to ODBC.

Chris

Occasional Contributor
Posts: 6

Re: Using DDE to open and close xlsX - disable compatibility pop up

Posted in reply to ChrisHemedinger

We have sas 9.1 I'll have a look at the ODBC route.

Thanks

Super User
Posts: 5,430

Re: Using DDE to open and close xlsX - disable compatibility pop up

Posted in reply to andrewjmdata

"our version of sas" - consider upgrading...

Data never sleeps
Occasional Contributor
Posts: 6

Re: Using DDE to open and close xlsX - disable compatibility pop up

If only, I am a small contractor minnow in a sea of very large UK bank:smileymischief:

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 634 views
  • 4 likes
  • 4 in conversation