I have had several occasions when this would have been useful but I hesitated at implementing it in our process so as not to introduce unexpected and hard to trace bugs. Thanks, David. Hi David, I'm not sure what's happening for you here. I think that's the default behavior, at least from what I've seen. The first row is treated as column names. NOTE: The import data set has observations and 91 variables. I don't see that behavior when the column names are in the first row of the sheet. You might need to track this with SAS Technical Support, as the issue might be triggered by your particular data.
Does this happen with all Excel sheets for you? Good info. Is there any easy way to remove the spaces in the dataset names once brought in if they exist? Thanks, Chris!
Worked marvelously. An example of this for the xl library example above would be very helpful. Thanks for posting this. Is there a roadmap for XLSX engine to provide member level status to the named ranges, of either worksheet or workbook scope, or named tables? Hi Richard -- good question.
Find helpful customer reviews and review ratings for Code Name "JET" (The Jet Files Book 1) at nuipamerighchris.ga Read honest and unbiased product reviews from. This book is a crime drama. It is the story of one woman's struggle to climb the government's secret agent's corporate ladder and this last case puts her, finally.
I'm not aware of a project for that, but it would be a good suggestion if you want to submit it to the SASWare Ballot. What is it that it cannot recognize the individuals sheets as member type?
Hi Stanford, I'm not sure why -- what are the member names sheet names? However what if the files on the Windows Server are available on the Linux service via a Samba share? Thanks for the article. I am trying to update a sheet using this method but Iget the error below. Do you know any solution? I get the same error "can not replace sheet because it has existing formulas that reference other pages" on some, but not all, of my Excel destination export sheets using the XLSX DBMS for proc export.
It seems to affect random sheets, but consistently affect the same sheets. I've checked several times that the sheets do not have formulas embedded. Their cells are referenced from other sheets within the workbook, but this is true also for other destination sheets where the proc export executes without fail. Even if I replace the sheet the error will subsequently occur. Nothing apparently funny about the sheet names associated with this error either. I think the message is issued if an existing sheet has any reference to other content in the workbook such as a link to another sheet.
In the XLSX file, there is some metadata of sheet references called a "calculation chain", and if there is any reference from your target sheet, the XLSX method errs on the side of caution and won't overwrite. What's weird about you described as the references problem is that I can "fix" it by doing the following. Let's say there are two sheets in the Excel book A and B. The proc export writes to sheet B and sheet A has formulas with cell references to sheet B.
No formulas on sheet B. If the proc export fails with this error above , I can create a new sheet "Bx" and successfully use a proc export to sheet Bx. Then change the formulas on sheet A to reference cells on sheet B2 instead, then the proc export will continue to work on subsequent attempts. It can't be the existence of formulas pointing to an export sheet, but something as you say about Excel's underlying metadata for a particular sheet that comes into conflict with SAS in circumstances I have yet to pinpoint.
I have a similar issue. Would you know if a pivot table built with a data source coming from a proc export in SAS is considered a reference? I'm not using any formula, but rather a pivot table. What's it is odd is that even when I remove all the pivot tables associated with the Excel table, the export just won't work. It basically seems quite random why it works or not, since sometimes it does, but I'm losing hope in finding the solution for this one.
I need consistency to run the report automatically, can't just expect it works. Experts might be able to help. Can you please illuminate me or point me to the doc that says which ones are or are not supported?
The XLSX engine allows the sequential reading of data only. That is, it does not support random access. It does not support certain tasks that require random access, such as the RANK procedure, which requires the reading of rows in a random order.
I'm not sure which data set options are specific to random-access engines, but I guess the limitations are similar to those for the XML engines. I'm afraid I've run into the gotcha as was noted in the original post when trying to "view" the xlsx sheet while in EG. EGP's original location on Linux btw. I was also prompted to save an autocall macro to it's original location, which I did.
From there, I noticed the. EGP file disappeared from the folder directory as I viewed it on Linux. The entire.
I tried to recapture the EGP from the location assigned as the Project Recovery area on Windows, but the only thing saved there, was the last copy of a program I had last executed, nothing else from the EGP. I have not been able to find reference to this known bug so any help like what is the safest way to interrupt EG at this point would be greatly appreciated.
We are runnnig this version of EG: 7. Sorry to hear that you lost work with this hangup. I don't know of a way to interrupt EG safely, other than killing the process, when you get this particular hang. I know it's a defect that the EG developers have on their list to fix I entered it, but I wasn't the only one. In the meantime, EG programmers need to be careful about direct access using this engine. How can i make other than 1th row as a variable row. My data start from 6th row as 5th row also has redundant data. I knew the option as name row in proc import for xls file but what option to use for xlsx file while importing from unix and windows environment.
Hi Chris, Does this approach allow to export variable labels instead of names? CMMC; set work. Many thanks Chris, I've to say I have saved your page on my favorites and do visit frequently, you're doing a great job in helping the intermediate programmers such as myself,. Thanks for this article. Super helpful. Quick question, do you know if it's possible to start writing data from the second row instead of the first?
Do you mean on the second row of the sheet -- or beginning with the second row of data? When i try to use xlsx engine and create a xlsx file from a sas dataset it gets created and at the same time it creates define name which has the range of the sheet. Now when i try to modify the sheet manually created in the above step by adding the more rows and columns and save it and try to convert this modified xlsx back to SAS dataset by referring the to the libname assigned using xlsx engine i do not get the updated data as per the updated xlsx sheet which i modified after xlsx was created by sas using xlsx engine in the above step.
It gives me observations and 2 variables where as i added one more column in the sheet manually after it got created in first step. This affects only the range definition -- it won't affect the data. Save the workbook and then SAS will be able to see the full sheet again, not just that predefined range.
Edit a formula would do but if we do all this using proc import then formula does not need to be edited in the scenario stated. This makes me think it is a defect.
Hi Chris, I tried to combine multiple excels - It worked. Do we have any way to preserve the existing formats? In other examples such as 'test' no spaces show up just fine in the datasets statement. So I'm wondering if the spaces are the problem.
Without the ability to do proc datasets statement I don't see how the Excel files can be opened in SAS Any way around the not recognizing sheet names, do you know? This should work, and it does in my test cases. Another approach you might try assuming a libname of "xLIB" :. When i try to read the xlsx file using proc import in SAS grid 9. Please contact technical support and provide them with the following traceback information:.
But when i try to run proc import in PC sas 9. Anything unusual about the file? Very large column names or other strange attributes? No around 59k records when i break it into two it works fine so no data issue. DO u have any idea what does segmentation error mean. Shouldn't happen, but sometimes does -- so working with Tech Support is the best way to get it resolved.