About PatMalarkey

PatMalarkey · ‎07-20-2022

ods excel has a formulas=on|off option! I just tried it and it works: SAS Documentation I'm running in EG. When I open the spreadsheet, cell A2 shows the value in Sheet2, cell B3. 🙂 data formula; formula = '=Sheet2!B3'; output; run; /*****************************************************************************/ /* */ /*****************************************************************************/ ods listing close; ods excel file = "/fmacdata/utility/npa/&ENV./Buybacks/output/Buyback_Test.xlsx" options (autofilter='all' embed_footnotes_once = 'on' embedded_footnotes='on' embed_titles_once = 'on' embedded_titles='on' formulas='on' frozen_headers='on' gridlines='on' sheet_interval='proc' sheet_name="Sheet1"); /*****************************************************************************/ /* */ /*****************************************************************************/ title; proc print data=formula noobs; run; ods excel options(sheet_name="Sheet2"); proc print data=ref_balances noobs; run; ods excel close; ods listing;

PatMalarkey · ‎03-14-2022

Wondering if anyone has tried using the Quickbase REST APIs from SAS? A co-worker and I are noodling with PROC HTTP with no luck so far. I know I'm missing the authorization token, but the papers available have me confused as to which other parameters I need. filename response "C:\Users\f353363\OneDrive - Freddie Mac\Section 404\NoCode\QuickBase\sasfiles\test.xml" lrecl=32000; proc http url="https://api.quickbase.com/v1/records/query?from=brumhp939,select=[6,7,8,10,11],where={9.EX.'623008'}" method='GET' out=response; run; ERROR: The TCP/IP tcpSockEstablishSSL() support routine failed with error 10054 (The connection was reset by a peer.). ERROR: The tcpSockRead call failed. The system error is 'The connection was reset by a peer.'. ERROR: Call to tcpSockContinueSSL failed. Any ideas are welcome! Thanks!

PatMalarkey · ‎03-04-2022

So was the fix to the content of the variable? All three datasets have a column, disclosed_element,All three datasets have a column, disclosed_element, that is created by putting a column named variable into a format I created $LLD. put(variable,$LLD.). For the green datasets, $LLD. translates variable into a Yes/No flag; for the red dataset (from an earlier iteration of development), $LLD. translates variable into verbiage, for example: NET SALES PROCEEDS. So, yes, the fix was to the content of the variable. What content did it have before instead of Yes/No? for example: NET SALES PROCEEDS Did you try just writing another file with that content to XLSX engine? Did it cause the same issue? Why do this? Let's peace out on this one.

PatMalarkey · ‎03-04-2022

The answers to your questions are, for the most part, in my post.

PatMalarkey · ‎03-04-2022

I stumbled onto what I think is the solution and thought I'd share it - very interesting and worth thinking about if you get the same error. Log snippet (from above): 3221 data xlout.NUM_ALL; 3222 length id_bus_execn $9.; 3223 set fat.NUM_ALL_&ID_PRIOR_EXECN 3224 fat.NUM_ALL_&id_bus_execn 3225 fat.rolling_&benchmark.mos; 3226 run; All three datasets have a column, disclosed_element, that is created by putting a column named variable into a format I created $LLD. put(variable,$LLD.). For the green datasets, $LLD. translates variable into a Yes/No flag; for the red dataset (from an earlier iteration of development), $LLD. translates variable into verbiage, for example: NET SALES PROCEEDS. When I went back and updated the red dataset so that it also had disclosed_element as a Yes/No flag, the problem went away. Not sure what's going on here under the covers and I found the solution by accident. 😕

PatMalarkey · ‎02-24-2022

But I'm not using EXPORT. I'm writing to an Excel file that I've allocated with a LIBNAME statement. Unless they're the same under the covers...

PatMalarkey · ‎02-24-2022

Hi, Tom! FAT is a SAS library and the LIBNAME points to a Unix directory - it's not using the XLSX engine. I will try reading the datasets one at a time and see if that tells me anything. Not sure how to see what encoding either the data or SAS session are using? Thanks!

PatMalarkey · ‎02-24-2022

All - I've seen here in Communities that this error happens when using PROC EXPORT and when reading from an Excel file using LIBNAME, but I'm trying to write to Excel. Note that the Excel file was created and that I can open it. The problem is that the shl script I use to run the program will send a "Job ended in error" email to the user. Any ideas? Thanks! LOG Snippet: NOTE: Libref XLOUT was successfully assigned as follows: Engine: XLSX Physical Name: /fmacdata/utility/npa/dev/Profiling/Monthly/Output/Profiling_1910_1936.xlsm 3217 /*****************************************************************************/ 3218 /* Send the profile data to XL */ 3219 /*****************************************************************************/ 3220 3221 data xlout.NUM_ALL; 3222 length id_bus_execn $9.; 3223 set fat.NUM_ALL_&ID_PRIOR_EXECN 3224 fat.NUM_ALL_&id_bus_execn 3225 fat.rolling_&benchmark.mos; 3226 run; ERROR: string is duplicated |><t/>| NOTE: There were 15876 observations read from the data set FAT.NUM_ALL_1910. NOTE: There were 16200 observations read from the data set FAT.NUM_ALL_1936. NOTE: There were 16848 observations read from the data set FAT.ROLLING_12MOS. NOTE: The data set XLOUT.NUM_ALL has . observations and 17 variables. NOTE: The export data set has 48924 observations and 17 variables. NOTE: DATA statement used (Total process time): real time 3.46 seconds cpu time 3.39 seconds

PatMalarkey · ‎09-30-2021

Hi, @Mark2010 ! The link in the book includes the ZIP file - the link I provided was my trying to get around the issue I had clicking the full link. I also checked my download directory and the ZIP file is not there. That said, when I got your reply via email with the full link to the ZIP file, I clicked it for grins. Darn, if I didn't get the download! Thank you! So, the link in the book still doesn't work for me, but I have the ZIP file, so the problem is solved as far as I am concerned. 😉 Thanks, again! 🙂

PatMalarkey · ‎09-29-2021

When I click the link in the SAS Certified Specialist Prep Guide to open / download the practice data ZIP file, a new tab opens in my Chrome browser and then it closes right away. No file opens or downloads. I was thinking this is because I'm at work, so I tried it also on my home laptop with no joy. Just plugging https://support.sas.com/content/dam/SAS/support/en/books/data/ into the browser gets a "Forbidden - Cannot serve this request" message. I'm kind of out of ideas. 😞

PatMalarkey · ‎05-03-2021

I see that now! Thanks! Link: https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-excel-multiple-sheet/td-p/225377

PatMalarkey · ‎05-03-2021

No. The values are incorrect when I use the LIBNAME method. They get rounded and they should not be. ODS is one work-around, but it is less than optimal.

PatMalarkey · ‎05-03-2021

Here is a snip from a log for a program that ran on our linux box this morning: NOTE: Copyright (c) 2002-2012 by SAS Institute Inc., Cary, NC, USA. NOTE: SAS (r) Proprietary Software 9.4 (TS1M3) NOTE: This session is executing on the Linux 2.6.32-696.30.1.el6.x86_64 (LIN X64) platform.

PatMalarkey · ‎05-03-2021

I was able to use ODS Excel to write the sheet with the issue to Excel and the percent retains the correct format. I need to write multiple sheets to the workbook, so it's not a complete solution - though I was able to write the other sheets using LIBNAME.

PatMalarkey · ‎05-03-2021

Please see my response to Reeza.

Online Status	Offline
Date Last Visited	‎01-10-2025 06:57 PM

Re: Writing Excel formulas in SAS using ods

proc http: Trying to access Quickbase

Re: ERROR: string is duplicated |><t/>| when using LIBNAME to write t...

Re: ERROR: string is duplicated |><t/>| when using LIBNAME to write t...

Re: ERROR: string is duplicated |><t/>| when using LIBNAME to write t...

Re: ERROR: string is duplicated |><t/>| when using LIBNAME to write t...

Re: ERROR: string is duplicated |><t/>| when using LIBNAME to write t...

ERROR: string is duplicated |><t/>| when using LIBNAME to write to an...

Re: Can't open the practice data zip file from the link in the SAS Cer...

Can't open the practice data zip file from the link in the SAS Certifi...

Re: Macro quoting for a url

Re: Accessing Quickbase from SAS 9.4 (Maint 3) - Has anyone done this?

Re: ERROR: string is duplicated |><t/>| when using LIBNAME to write t...

Re: ERROR: string is duplicated |><t/>| when using LIBNAME to write t...

Re: Can't open the practice data zip file from the link in the SAS Cer...

Re: Writing Excel formulas in SAS using ods

proc http: Trying to access Quickbase

Re: Can't open the practice data zip file from the link in the SAS Cer...

Re: Writing Excel formulas in SAS using ods

proc http: Trying to access Quickbase

Re: ERROR: string is duplicated |><t/>| when using LIBNAME to write t...

Re: ERROR: string is duplicated |><t/>| when using LIBNAME to write t...

Re: ERROR: string is duplicated |><t/>| when using LIBNAME to write t...

Re: ERROR: string is duplicated |><t/>| when using LIBNAME to write t...

Re: ERROR: string is duplicated |><t/>| when using LIBNAME to write t...

ERROR: string is duplicated |><t/>| when using LIBNAME to write to an...

Re: Can't open the practice data zip file from the link in the SAS Cer...

Can't open the practice data zip file from the link in the SAS Certifi...

Re: Unix SAS libname xlsx rounds numbers - I don't want it to

Re: Unix SAS libname xlsx rounds numbers - I don't want it to

Re: libname xlsx rounds numbers - I don't want it to

Re: Unix SAS libname xlsx rounds numbers - I don't want it to

Re: libname xlsx rounds numbers - I don't want it to

SAS Analytics Explorers