cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BenConner
‎06-06-2023
  • 218 Posts
  • 5 Likes Given
  • 2 Solutions
  • 24 Likes Received
About
SAS developer for 31 years, from version 5.13. SAS efficiency techniques;

Re: creating thicker borders around the outside of a report using exce...

by Pyrite | Level 9 in ODS and Base Reporting
‎04-23-2014 06:50 PM
‎04-23-2014 06:50 PM
Thanks, Cynthia!  Will do.  Just wanted to make sure it wasn't something I was overlooking. Best wishes, Ben ... View more

creating thicker borders around the outside of a report using excelxp

by Pyrite | Level 9 in ODS and Base Reporting
‎04-23-2014 01:18 PM
‎04-23-2014 01:18 PM
Hi, I've been trying different options to create a 3pt border around proc report output going to an excel spreadsheet and am coming up empty.  I need the entire report surrounded by a 3pt border. The code I've been using is below.  I thought I would be able to get it with the style(report) option but that had no effect.  The other border thicknesses are working fine (between columns, vendors, etc.) Thanks! --Ben ods tagsets.excelxp file='c:\test.xml' style=default; proc report data=in.table3 nowd style(hdr)={background=darkblue borderwidth=1pt bordercolor=black borderstyle=solid                                             font_face=Calibri fontsize=12pt color=white tagattr='rotate:-90'}                                 style(report)={borderbottomwidth=3pt}                                 style(column)={backgroundcolor=white borderwidth=1pt bordercolor=black borderstyle=solid};      where question <=24;      columns ( ' ' vendor_id) ('Month' month ) ( 'Records' records) question,count dummy;      define vendor_id                        / group order=data '' style={borderleftwidth=3pt borderrightwidth=3pt};      define month                            / group '';      define records                          / analysis max '' style(column)={tagattr='format:#,##0'};      define question                         / across  order=data '';      define count                            /   ''            style(column)={tagattr='format:#,##0'};      define dummy                            / computed noprint;      compute dummy;         if vendor_id ^= ' ' then do;            call define(_row_,'style','style={bordertopwidth=3pt}');            end;      endcomp;      format vendor_id    $vendors.             question     question.             month        month.             count        comma9.;   run; ods tagsets.excelxp close; ... View more

Re: Can sas actually give me real time data

by Pyrite | Level 9 in SAS Data Management
‎01-21-2014 02:50 PM
3 Likes
‎01-21-2014 02:50 PM
3 Likes
There's really only two ways to accomplish this--having SAS check on a recurring basis per the above quote, or having Oracle execute an external procedure when a new row gets inserted in the table.  That procedure could be as simple as running a batch job/script which used SAS to process the new rows.  That said, I don't know enough about Oracle to advise on how to do that.  I'm sure others can provide the details. --Ben ... View more

Re: Replacing a sheet in an excel workbook

by Pyrite | Level 9 in Microsoft Integration with SAS
‎10-02-2013 02:59 PM
‎10-02-2013 02:59 PM
Found the answer to the question I posed; apparently the technique illustrated is very specific to .xls formatted Excel files.  I ended up using a Proc Export with a Replace option, but had to name the tab and the columns on that tab with a named range identical to the tab name.  Example: Proc Export data=work.scores    outfile='c:\temp\classes.xlsx'    dbms=EXCEL    Replace;   sheet='classscores'; run; The tab classscores had a named range of classscores defined on it as well.  SAS was able to delete and replace the contents nicely. Many thanks to Sandy O in SAS' tech support for pointing me in the right direction. --Ben ... View more

Replacing a sheet in an excel workbook

by Pyrite | Level 9 in Microsoft Integration with SAS
‎10-01-2013 01:25 PM
‎10-01-2013 01:25 PM
Hi, Looking through Art Carpenter's Innovative SAS Techniques, on page 7 he shows a technique for updating a sheet in an excel workbook.  In this example he used an Office 97/2003 type workbook (.xls). Tried to duplicate this with a current style workbook in Windows SAS 9.2 and got an error. I created a test workbook (test.xlsx) with a single sheet named 'test'. I then used the following libname to get to it: libname testx excel '...path to sheet\test.xlsx' scan_text=no; Ran a proc datasets to delete the file (had to use a $ after the name--it couldn't find the sheet name just as 'test'): proc datasets library=testx nolist;   delete 'test$'n;   quit; Then used a simple data step to try to replace it: data testx.'test$'n; set temp; run; The error I got back was: ERROR: The MS Excel table test$ has been opened for OUTPUT.  This table already          exists, or there is a name conflict with an existing object.  This table          will not be replaced.  This engine does not support the REPLACE option. NOTE: The SAS System stopped processing this step because of errors. Suggestions? Thanks much! --Ben ... View more

using NTFS compression with SAS datasets

by Pyrite | Level 9 in SAS Programming
‎09-20-2013 01:50 PM
‎09-20-2013 01:50 PM
Hi, Has anyone used the NTFS compression option on folders with SAS datasets in them?  Has this helped execution, been a problem or resulted in issues? It uses LZ compression which typically will save a lot of space, but there are times when MS recommends against using it. Thanks! --Ben ... View more

Re: Suppressing the var= prefix when using ExcelXP and bygroup process...

by Pyrite | Level 9 in ODS and Base Reporting
‎09-17-2013 02:07 PM
‎09-17-2013 02:07 PM
Hi Reeza, Didn't know there was TOC tagset functionality.  Inherited this code, so it may not have existed when it was written.  Will take a look at it.  As it is, the suggestions you and Cynthia made eliminated the need for macro looping, saving a ton of time and several hundred pages of log files. Much appreciated! --Ben ... View more

Re: Suppressing the var= prefix when using ExcelXP and bygroup process...

by Pyrite | Level 9 in ODS and Base Reporting
‎09-17-2013 11:11 AM
‎09-17-2013 11:11 AM
Hi Cynthia, Apparently the security restrictions on the system I work on prevent me from pasting into a web browser.  I'll retype the code I'm using, omitting the details within the proc reports... ods tagsets.ExcelXP close; ods tagsets.ExcelXP   options(embedded_titles='yes'  embedded_footnotes='no' width_fudge='0.75' center_horizontal='yes' autofit_height='yes' skip_space='0' row_repeat='2' row_heights='47, 15, 15, 15, 15, 15, 15' pagebreaks='no' scale='85')   file='test.xml' style=journal; * Table of Contents with Hyperlinks; ods tagsets.ExcelXP    options(sheet_interval='none' sheet_name="Contents" default_column_width="9, 40, 9"); proc report data=user.nbr_vendors nowindows;   column vendor_id organization_name vendor_type;   <series of define statements>;   compute vendor_id;       vend_ref="#'"||trim(left(vendor_id))||"'!A1";       call define(_col_,'URL',vend_ref);    endcomp; run; ods escapechar='^'; ods noproctitle noptitle; options byline; ods tagsets.ExcelXP    options(sheet_name=" " sheet_label=" " suppress_bylines="yes" sheet_interval="bygroup" default_column_width="25,9, 6, 6, 6, 6, 6, 6"); title '#byval1 -- #byval2'; proc report data=detail nowindows;   by vendor_id organization name;    column question response pct1 pct2 pct3 pct4 us_pct1 us_pct2;    <series of define statements>;    run;    quit; ods tagsets.ExcelXP close; Is there anything after the first proc report I can add to reset the sheet name without closing the xml file creation? Thanks! --Ben ... View more

Re: Suppressing the var= prefix when using ExcelXP and bygroup process...

by Pyrite | Level 9 in ODS and Base Reporting
‎09-16-2013 10:24 PM
‎09-16-2013 10:24 PM
Hi Cynthia, Sorry, left work several hours ago and don't have the code with me now.  I'll post it tomorrow morning when I get in.  Didn't mean to offend anyone. --Ben ... View more

Re: Suppressing the var= prefix when using ExcelXP and bygroup process...

by Pyrite | Level 9 in ODS and Base Reporting
‎09-16-2013 07:00 PM
‎09-16-2013 07:00 PM
Hi Reeza, Cynthia, These two parameter combinations worked great.  So I had to break them.   I uncommented a preceeding proc report step that has a hard-coded sheet name called 'Contents' where it generates a table of contents.  This resulted in the sucessive sheet names being called Contents2, Contents3, etc., ignoring the sheet_label and suppress_bylines values.  ?? I suppose I could generate the table of contents as the last sheet, then manually move it to the beginning of the list.  Would like to avoid that if I could, though. Thanks! --Ben ... View more

Suppressing the var= prefix when using ExcelXP and bygroup processing

by Pyrite | Level 9 in ODS and Base Reporting
‎09-16-2013 06:13 PM
‎09-16-2013 06:13 PM
Hi, I'm getting great results with the sheet-interval='bygroup' in generating separate tabs in Excel; the only hitch I'm struggling with is it includes the <variable name>= in front of each value.  I'd like it just to use the value itself as the tab name, as I have a table of contents with links to the different tab values. Is that possible? SAS vsn 9.2, Excel XP tagset v1.94, 09/09/12. Thanks much! --Ben ... View more

Re: Continue after errors

by Pyrite | Level 9 in SAS Programming
‎06-28-2013 12:47 PM
2 Likes
‎06-28-2013 12:47 PM
2 Likes
Hi, The intent here is to have a final 'Final Status' step at the end of the batch job to email the results of this job to someone who cares.  These options will allow that step to execute.  Thanks! The &syserr value gets reset after the final data step starts, so what I did was capture the value prior to that point.  I can use this now to accurately report the status of the job. Interestingly, the value of syserr changed from 1012 right after the first data step to a value of 3 later on.  Any ideas why? That was strange, but still acceptable. Thanks much! --Ben data test; infile '~/notfound'; input missing 6.; put _all_; run; %put syserr=&syserr; proc contents data=work._all_; run; %put syserr=&syserr; %let joberror=&syserr; options obs=1 nosyntaxcheck; data _null_; put "Last error found was: &joberror"; run; ... View more

recovering from error conditions in batch mode

by Pyrite | Level 9 in SAS Programming
‎06-27-2013 06:02 PM
‎06-27-2013 06:02 PM
Hi, I'm looking for a way to execute a data step at the end of a batch program only when an error popped up which triggered an obs=0 and syntax checking condition.  I thought there was an option for that but didn't find one.  This happens to be in 9.1.3 on a Solaris box, but am looking for a more generic guide. Thanks! --Ben ... View more

Re: Preserving long numeric values in tagsets.excelxp generated spread...

by Pyrite | Level 9 in Microsoft Integration with SAS
‎04-12-2013 03:39 PM
‎04-12-2013 03:39 PM
Outstanding!  That worked like a charm.  Thanks much! --Ben ... View more

Re: Preserving long numeric values in tagsets.excelxp generated spread...

by Pyrite | Level 9 in Microsoft Integration with SAS
‎04-12-2013 03:23 PM
‎04-12-2013 03:23 PM
Will give that a shot.  Thanks! --Ben ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎06-06-2023 11:03 PM
Likes given to
View All
Likes from
Quick links