BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
andrewjmdata
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

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."

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

View solution in original post

10 REPLIES 10
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

andrewjmdata
Obsidian | Level 7

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

andrewjmdata
Obsidian | Level 7

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

ChrisHemedinger
Community Manager

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."

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
andrewjmdata
Obsidian | Level 7

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!

ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
andrewjmdata
Obsidian | Level 7

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

Thanks

LinusH
Tourmaline | Level 20

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

Data never sleeps
andrewjmdata
Obsidian | Level 7

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 10 replies
  • 2165 views
  • 4 likes
  • 4 in conversation