About epp_dog

epp_dog · ‎01-15-2019

That is interesting. Slightly different from what I am experiencing where the actual source data does not change. the save date is not updated. nothing. just SAS thinking it wrote out correctly. And the real problem is that for me it is not consistent. Sometimes it will work and other times it will not. I am also outputting data to a raw named range that is referenced by the pivot table (that is important if you are not building your pivot table that way I would suggest changing). So far I am still stuck with saving the SAS dataset that I am writing to Excel. Checking Excel to see if it actually updated. And then building an abort process if it did not update. Not a fix but it does prevent me from sending bad data to users.

epp_dog · ‎09-04-2018

Writing to different sheets is not the same as writing to different named ranges. There can be several named ranges on the same worksheet. That is what is not currently supported. We build Excel pivot tables off the the output from SAS. In order for the pivot table to automatically grow/shrink with the raw data we reference named ranges in the pivot table definition. (I know we could reference the entire columns of raw data for the pivot table and then unselect the "BLANKS" but 1) bad user experience and 2) doesn't solve for when we output multiple ranges in the same sheet). Glad people are still thinking about this issue. It is strange that I have some spreadsheets that have this problem 2-3 times a week and others that go months between problems. I just don't see the pattern yet. from: https://exceljet.net/named-ranges A named range is just a human-readable name for a range of cells in Excel. For example, if I name the range A1:A100 "data", I can use MAX to get the maximum value with a simple formula: =MAX(data) // max value

epp_dog · ‎08-13-2018

I believe the DBMS=XLSX engine does not support named ranges in Excel - which my pivot tables use so that is not an option. I have good macros to check the spreadsheet after SAS thinks it writes to it and compare to the SAS datasets used to write to those named ranges. If they don't match, we send the programmer/admin a text to open/save/re-write the data. It is a 5 minute fix but annoying to have to do at all. My VB is not very good (i.e. really BAD) but I am thinking that if I can write a VB script to execute from within the SAS program, I can just always open/save/close the spreadsheet before attempting the write. It is really strange as I have some reports where this seems to happen 70-80% of the time and others where it seems to only happen 10-20% of the time. And I have never been able to force the error to happen either (tried just looping a open/write/close thousands of times to see if it could hit this error).

epp_dog · ‎08-06-2018

Sadly, our VA server got turned off accidentally shortly after this question and our IT team was never able to resurrect it. I had poor user adoption on SAS VA so I couldn't get executive support to get the machine running again. So figuring this out was never again a priority. We are in the midst of installing Viya and hope that I can get better adoption/usage this time. Sorry I'm no help at this time.

epp_dog · ‎05-21-2018

I have the same issue with a "silent fail" writing to an existing spreadsheet/named range where the corrective action is to manually open/save the file. That makes me think that this is a problem with a cached version of the file or metadata information perhaps and the manual save is refreshing/replacing the cached version? (just guessing there) My master spreadsheet is in a protected folder so no users can open it so it doesn't get touched by any SAS procedure or myself between runs (don't know what windows/antivirus stuff happens between runs though). I posted a similar question several months ago and all the suggestions were on ways to identify that the spreadsheet has not updated (which we have now done to prevent incorrect reports from going to users but doesn't really fix the issue.) I wonder if issuing a system command to open/save the file right before the export would help fix this (we technically use a libname statement with data steps writing to the existing named ranges after deleting the data from those ranges) . Problem is that I have yet found a way to reliably reproduce the error so I could test this as it can be months between this error happening.

epp_dog · ‎04-11-2017

Thanks Patrick, You are right, data isn''t updated and the file "save timestamp" isn't updated either. I agree, your suggestion is a bit of voodoo but it might just work (and in my 20 years of SAS coding I certainly have come to accept some strange solutions) . I have always assumed it to be more of a windows/excel issue than SAS. I'll play around with your suggestion of double checking the values in the spreadsheet as that would eliminate the embarassement of sending the client last week's report! David

epp_dog · ‎04-10-2017

There are actually about 20 named output ranges and I didn't want to bore everyone with all of them but NONE of them got written yesterday night. That one is just where I am writing out a dataset (raw_dates) of all the date macros I used during the running of the program. (So I have a datastep earlier where I write rows to store off each value). When this happens, if I do not open the spreadsheet and do the "add something/delete/save" and instead just rerun the wrote to Excel portion, it does not update the data.

epp_dog · ‎04-10-2017

I have an intermittent issue that is baffling me. SAS EG Version 7.13 (7.100.3.5408) (64-bit) Excel 2013 We use a LIBNAME statement to access an existing Excel workbook that is in a production area so team members cannot access it (i.e. no person has the file open during run time). We run PROC DATASETS to delete the desired excel named ranges. We then use DATA steps to write to those named ranges. Finally we clear the libname. 98% of the time everything works just great. But occasionally the program will finish with no issues/warnings/errors noted in the log (actually, the log tells me that the records were written to the named range) but the named ranges have not been updated and the "Date modified" of the Excel file in windows explorer is also from the prior week. Whenever this happens we simply open the workbook, go to a blank cell outside of a named range, enter something, delete what we just entered, resave and close the workbook. Then rerunning the SAS code will update the named ranges. This seems like this is likely more a windows/excel issue more than SAS but the output is going to a client via an automated job so every few months they receive the prior week's report. Wondering if anyone else has come across this and what they discovered was the actual root cause. Thanks David Abbreviated log file below: LIBNAME finance EXCEL "S:\Prod_Reporting\SAS_Projects\output\&OUTFILE..xlsx"; NOTE: Libref FINANCE was successfully assigned as follows: Engine: EXCEL Physical Name: S:\Prod_Reporting\SAS_Projects\output\finance_reports_MTD.xlsx *** clear named ranges; run; PROC DATASETS LIB=finance; DELETE raw_dates; QUIT; NOTE: Deleting FINANCE.raw_dates (memtype=DATA). *** write data to named ranges; run; *** Date Macros ***; DATA finance.raw_dates (rename=(period="Report Period"n value ="Date Value"n)); SET raw_dates; RUN; NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables. NOTE: There were 15 observations read from the data set WORKSAVE.RAW_DATES. NOTE: The data set FINANCE.raw_dates has 15 observations and 2 variables. LIBNAME finance CLEAR; NOTE: Libref FINANCE has been deassigned.

epp_dog · ‎03-27-2014

Thanks for the reply Ted. Unfortunately that doesn't quite work. For this exercise I care less about WHICH campaigns but rather HOW MANY campaigns. So as I drag the slider Account #1 goes from being in 8 campaigns to only 2 or 3 depending upon the dates. Others may go up, depending upon where the sliders are at. The aggregate count does a perfect job of classifying everyone by the number of unique campaigns they are in during that period. However, I don't see a way to move that aggregate measure to the category section. Meaning I don't care about how many campaigns account #1 was in but rather how many accounts were in only 1 campaign during that time period (or 2, 3, 4,... campaigns). To put this into something we all experience, think of your favorite retailer who sends you emails. I want to say "for the month of March, retailer X sent 20 emails to 100 people, 19 emails to 85 people, etc." Your bottom graph did give me an idea that I could still put the actual accounts as the category variable and let the natural sort group them together. That does allow for visually seeing how many campaigns people were in (especially if I could use colors but those are only available for categorical variables) but I don't know how that will work when I have 6 million accounts - I don't think VA will want to throw that many categories into a bar chart.

epp_dog · ‎03-26-2014

I am working with an email history table: campaign id campaign send date account ID What I want to do is present a slider based on campaign send date and then display how many people have received 1 campaign in that time period vs 2, 3, 4, etc. An aggregated value to "count distinct" account ID gets me started as it does accurately reflect the number of campaigns a person has received based on the slider range but I want to twist this data. Instead of showing that account 1 had 6 campaigns I want to say that 1 person had 1 campaign, 0 people had 2 campaigns, 1 person had 3 campaigns, etc. Any ideas on how to proceed? Sample data: acct_id camp_id send_date 1 EM-1 10/15/2013 1 EM-2 10/22/2013 1 EM-3 10/29/2013 1 EM-4 11/5/2013 1 EM-5 11/12/2013 1 EM-6 11/19/2013 1 EM-7 11/26/2013 1 EM-8 12/3/2013 2 EM-1 10/15/2013 2 EM-3 10/29/2013 2 EM-4 11/5/2013 2 EM-5 11/12/2013 2 EM-7 11/26/2013 2 EM-8 12/3/2013 3 EM-1 10/15/2013 3 EM-5 11/12/2013 4 EM-3 10/29/2013 4 EM-4 11/5/2013 4 EM-6 11/19/2013 4 EM-7 11/26/2013 4 EM-8 12/3/2013 4 EM-9 12/10/2013 5 EM-1 10/15/2013 5 EM-2 10/22/2013 5 EM-4 11/5/2013 5 EM-5 11/12/2013 5 EM-7 11/26/2013 6 EM-2 10/22/2013 6 EM-3 10/29/2013 6 EM-4 11/5/2013 6 EM-6 11/19/2013 6 EM-7 11/26/2013 6 EM-8 12/3/2013 6 EM-9 12/10/2013 7 EM-1 10/15/2013 7 EM-3 10/29/2013 7 EM-5 11/12/2013 7 EM-8 12/3/2013 7 EM-9 12/10/2013 8 EM-1 10/15/2013 8 EM-2 10/22/2013 8 EM-3 10/29/2013 8 EM-5 11/12/2013 8 EM-7 11/26/2013 8 EM-8 12/3/2013 8 EM-9 12/10/2013

Online Status	Offline
Date Last Visited	‎04-11-2020 01:56 AM

Re: Proc Export silently fails

Re: Proc Export silently fails

Re: Proc Export silently fails

Re: How can I use an aggregated measure as a category?

Re: Proc Export silently fails

Re: Excel named ranges intermittently not saving

Re: Excel named ranges intermittently not saving

Excel named ranges intermittently not saving

Re: How can I use an aggregated measure as a category?

How can I use an aggregated measure as a category?

Re: Excel named ranges intermittently not saving

Re: How can I use an aggregated measure as a category?

Re: Proc Export silently fails

Re: Proc Export silently fails

Re: Proc Export silently fails

Re: How can I use an aggregated measure as a category?

Re: Proc Export silently fails

Re: Excel named ranges intermittently not saving

Re: Excel named ranges intermittently not saving

Excel named ranges intermittently not saving

Re: How can I use an aggregated measure as a category?

How can I use an aggregated measure as a category?