Thank you so much for the video, it really help me! :) I have a question, i did the same code as you with the proc export and create a folder in my C drive (windows 10), but it doesn´t work, there is a note that says: [ERROR: Temporary file for XLSX file can not be created -> /pbr/biconfig/940/Lev1/SASApp/c:\sasito\/cars.$$1. Make sure the path name is correct and that you have write permission.] Do you know how to fix it?
Ignacia, can you send a full screen shot of your SAS session with your SAS log message to curriculumconsulting@sas.com ? The fact that there is SASApp in the resolved path may indicate you using SAS Enterprise Guide connecting to a server version of SAS, but we would like to confirm. It is difficult to troubleshoot without seeing your code or knowing how you are using SAS.
We cannot provide step by step instructions or detailed code in this RU-vid feedback area. Your best resource for this type of question is SAS Technical Support because they can look at your license and determine the best way to do the export you're asking about. You also asked whether you can write code for macros and it's not clear whether you mean Excel macros or the SAS Macro facility. Your best resource for this type of question would either be the SAS Community Forum or SAS Tech Support. To open a track with Tech Support, fill out the form at this link: 2.sas.com/6059yVl1N . To post in the SAS Community Forum we would recommend either the Enterprise Guide forum: 2.sas.com/6050yVl14 or the SAS Programming Forum: 2.sas.com/6051yVl1f .
There are a few options in SAS Studio - You can change your "report" output to Excel here: 2.sas.com/6051PtVNf. Otherwise, you will need to use code (PROC EXPORT with DBMS=XLSX) and then download the resulting file in your browser. Hope that helps! Please let us know if you have any other questions!
You can export into a named range using PROC EXPORT. However, the method you use is going to depend on the version of SAS that you're using, the version of Excel and whether you have identified named ranges in your Excel sheet. This is the type of question that is better answered by opening a case with SAS Tech Support or posting in one of the SAS Community Forums such as the SAS Programming Forum, here: 2.sas.com/6053PoZHJ.
Sanjeev, thank you for your inquiry! We can't provide Tech Support here in the RU-vid feedback area, but we do offer some resources that can provide you further assistance. For usage questions we recommend posting your question in SAS Communities. Here are some tips to help you get started 2.sas.com/6056KYPRk
@@SASUsers Sure I have a path error . ERROR: Temporary file for XLSX file can not be created -> /pbr/biconfig/940/Lev1/SASApp/C:\demo\/cars.$$1. Make sure the path name is correct and that you have write permission. ERROR: Export unsuccessful. See SAS Log for details.
SAS can use an "informat" to read a date that looks like this: 06/24/2024 into a SAS variable in a SAS data set. Or, you can use a date constant in the form '24Jun2024'd to create a date value in a program. However, that "readable" date, for a variable value, will be stored internally as the number 23551 because SAS date values are stored internally as the number of days from Jan 1, 1960. Jan 1, 1960 is internally stored as 0; Jan 2, 1960 is internally stored as the number 1; Dec 31, 1959 would be stored as the number -1; July 4, 1776 would be stored as the number -67019 (because it occurred BEFORE Jan 1, 1960, it is stored as a negative number) -- so you see what we mean when we say that SAS stores date values as the number of days from Jan 1, 1960. When you want to display a SAS date value, you need to use a FORMAT so that SAS knows what display format to use so that your internally stored number is displayed as 06/24/2024 or 24Jun2024 or 2024/06/24, or any one of the date FORMATs that you will find available in the SAS documentation. To display your internally stored value in a "readable" date form, you would use a FORMAT statement to tell SAS what date format you want to have used to display the internally stored number. If you want to learn more about displaying dates with SAS formats, I recommend taking our free Programming 1 class, which discusses formats and using date constants in your code. In addition, here are some Community Forum postings that talk about using SAS date values and formatting them: 2.sas.com/6057g5s5x 2.sas.com/6053g5s5N 2.sas.com/6055g5s5f === ===
Thank you, really helpful. How can I export to Excel into a specific cell? How can I define the column and row that I want to move the database into, within an existing Excel?
Hi: This video does not show you how to export into a specific cell. The best you can do with the techniques shown in this video, using PROC EXPORT is to export to a named range in an existing Excel workbook. However, this RU-vid feedback area does not allow us to post code or screen shots, your question would best be answered by working with SAS Tech Support since the approach for what you want to do can be version specific to the version of SAS and Excel that you're using. To open a track with Tech Support, fill out the form at this link: 2.sas.com/605236EGQ and let them know your license number for SAS and your version of Excel. Or, as an alternative, you might consider posting your question in the SAS Community Forums. Here are some examples of previous postings on related questions: 2.sas.com/605336EGv or 2.sas.com/605436EGa . Some of these postings are older so you might want to post a new question to see whether others have any new insights.
is the 3rd strategy (proc export) the same command that is used in Enterprise Guide when you right click on a dataset and select export..? I am seeking alternatives to the GUI export because the process is much too slow. i need to write the sas data into a csv format so i can share it with external stakeholders. any tips to speed up the export is greatly appreciated!
Hi Papadharma, SAS Enterprise Guide uses the Microsoft engines (ACE or JET engine depending on your version of EG) when you use the GUI interface to do an export. PROC EXPORT, as demonstrated in this example uses the SAS DBMS= value to do the export. You can export SAS data to a CSV file using PROC EXPORT or, if you have SAS/Access to PC File Formats at your site, you can use PROC EXPORT to export from SAS data tables directly to an XLSX file. We cannot post code or screen shots in this feedback area. So if you need more help with exporting using code, then we recommend that you open a track with SAS Technical Support.
I did use the same data step procedure as you did so it does read or connect to my xlsx with the libname, however when i run it, it tells me that "ERROR: File X.class.DATA does not exist". I am using windows 10 and also i am using the right version of SAS 9.04.01M6P110718 on my computer.
We're sorry you are having trouble! We checked with Kathy Kiraly from the video, and she asked for you to please send an email to SAS_Cares@sas.com with the details below: 1) Please send us the code you are using when you get the error about 2.sas.com/60531n1RF not existing. 2) Please run this code and send us the results: PROC PRODUCT_STATUS; RUN; Thank you!
Hello, Sophia. We're sorry to hear that! Will you please follow the same instructions we gave above? Please send an email to SAS_Cares@sas.com with the details below: 1) Please send us the code you are using when you get the error about 2.sas.com/60531n1RF not existing. 2) Please run this code and send us the results: PROC PRODUCT_STATUS; RUN; Thank you!
@@SASUsers Thanks. I just started a new session and used the libname option. I'm trying it out. It spits out the following error: 1 %put my version: &sysvlong; my version: 9.04.01M3P062415 2 option validvarname=v7; 3 libname x xlsx "G:\My Drive\KarenLiu\sascode\sample.xlsx"; ERROR: The XLSX engine cannot be found. ERROR: Error in the LIBNAME statement.
I'm sorry to hear that you are still having trouble. Do you know your site number? Can you send a copy of your log to SAS_Cares@sas.com so we can look into this further for you? Thanks!
Hi: The scope of this video is on using SAS to export SAS data to an Excel file, not on generating ANOVA results. However, whether you are using the techniques shown in the video or analyzing your own data, one fundamental feature of SAS is the use of the LIBNAME statement. In this video, the narrator uses a LIBNAME statement to point SAS to an Excel file. If your data are in Excel, in an XLSX file, then you would use a similar LIBNAME statement to point to your Excel file. If your data are in a SAS data set, then the LIBNAME statement (you show MYDATA as the library name) needs to be issued to point to that file. We cover the use of the LIBNAME statement for SAS data and for Excel in our Programming 1 class. It's not possible to provide a full explanation of the LIBNAME statement and how to use it in this comment. Our suggestion is that you either review the LIBNAME explanations in Programming 1 or post your question in the SAS Community Forums for more in-depth help.
Hi Xiaoyu, Yes, you can use a temporary SAS data set in both PROC EXPORT and when using the LIBNAME statement. proc export data=work.temp ... libname myx xlsx 'mypath/employees.xlsx'; data myx.newsheetname; set work.temp; ... I hope this helps!
We recommend using ODS EXCEL for creating Excel files that contain data tables and charts. You can see more in this section of this EG tutorial. 2.sas.com/6051z37qU
@@SASUsers follow up question, is there a way to edit the layout of how the visuals will be displayed on Excel? I am able to pull them to one or multiple sheets. But I would like some to be located side by side instead of just scrolling down. Each visual is made from different data. I would like some to be 2 next to each other and some be 3 next to each other.
@@christinacaudillo3250 Unfortunately the ODS EXCEL method does not support layout options like side-by-side or placing in specific areas. This would require some post processing in VB Script or PowerShell or similar. The SAS Add-In for Microsoft Office allows you to do more by pulling SAS content directly to specific locations in a sheet.
Definitely does NOT work for SASEG. As usual, just when I think SAS might be useful, it falls flat on its face. I'll be glad when my industry realizes how much money it's wasting on this overpriced relic. But no worries. R does exactly what I needed.
Hi @dlseller -- if you're using SAS Enterprise Guide, there are easier ways to share data/output to Excel. Check out this related tutorial on the SAS Users channel here: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-sTPIooPt1Mk.html
The video was illustrating using SAS Studio and writing code to do an export. Writing code like this should work in EG, but we can't post screen shots or videos in the RU-vid forum. Enterprise Guide has several ways, above and beyond what's shown in the video to do both importing and exporting between SAS and Excel. Even given what Chris responded, the bottom line is that ALL the techniques shown in the video will work in EG. The code that we show uses the XLSX Libname engine. Assuming the right server components and write-access permissions, the code shown in all 3 examples in the video should work in SAS Enterprise Guide.
I got this to work! The file path in the libname statment was much more complicated than I anticipated with our saseg setup. But I got it to work and I've already used it a couple of time this week.
@@dlseller maybe next time consider staying with the issue a litlte longer before bashing the software . Did you ask anybody at the SAS Communities forums? Glad you solved it, though.
