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
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."
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.
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.
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.
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
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."
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!
If you have SAS/ACCESS to PC Files, look at these techniques:
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
We have sas 9.1 I'll have a look at the ODBC route.
Thanks
"our version of sas" - consider upgrading...
If only, I am a small contractor minnow in a sea of very large UK bank:smileymischief:
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.