About PhilC

PhilC · ‎06-25-2019

Pro: it works. Kuddos: drop prior_: Con: To scale this up, we'd need a lag() function, if statement and prior_ variable for each column not in the by statement. I am looking for something else.

PhilC · ‎06-25-2019

Is there a way to use merge, modify, update or set to do the following data Left (sortedby=ID date); infile datalines; input ID $4. RDATE MMDDYY12. date MMDDYY10.; format RDATE date mmddyy10.; datalines; A998 06/01/2018 06/01/2018 B027 05/21/2018 05/21/2018 C001 03/31/2018 03/31/2018 D221 11/01/2017 11/01/2017 E379 10/07/2017 10/07/2017 F693 06/19/2018 06/19/2018 ; run; data Right (sortedby=ID date); infile datalines; input ID $4. INTVDT MMDDYY12. STA $1. date MMDDYY11.; format INTVDT date mmddyy10.; datalines; A998 09/30/2012 Z 09/30/2012 A998 03/11/2013 Y 03/11/2013 A998 07/31/2017 X 07/31/2017 A998 02/21/2018 W 02/21/2018 B027 05/17/2011 V 05/17/2011 E379 10/04/2017 U 10/04/2017 G032 03/01/2017 T 03/01/2017 ; run; data whole; set left Right; by ID date; run; This is the desired dataset. Where the bolded data are lagged, retained or it is as if the SET statement forgot to clear the data vector each record. ID RDATE date INTVDT STA A998 . 09/30/12 09/30/12 Z A998 . 03/11/13 03/11/13 Y A998 . 07/31/17 07/31/17 X A998 . 02/21/18 02/21/18 W A998 06/01/18 06/01/18 02/21/18 W B027 06/01/18 05/17/11 05/17/11 V B027 05/21/18 05/21/18 05/17/11 V C001 03/31/18 03/31/18 05/17/11 V D221 11/01/17 11/01/17 05/17/11 V E379 11/01/17 10/04/17 10/04/17 U E379 10/07/17 10/07/17 10/04/17 U F693 06/19/18 06/19/18 10/04/17 U G032 06/19/18 03/01/17 03/01/17 T Instead of this, as coded: ID RDATE date INTVDT STA A998 . 09/30/12 09/30/12 Z A998 . 03/11/13 03/11/13 Y A998 . 07/31/17 07/31/17 X A998 . 02/21/18 02/21/18 W A998 06/01/18 06/01/18 . B027 . 05/17/11 05/17/11 V B027 05/21/18 05/21/18 . C001 03/31/18 03/31/18 . D221 11/01/17 11/01/17 . E379 . 10/04/17 10/04/17 U E379 10/07/17 10/07/17 . F693 06/19/18 06/19/18 . G032 . 03/01/17 03/01/17 T

PhilC · ‎05-08-2019

Cynthia, I'm also sorry to say the Flow option actually forces line wrapping, that is the second kind. Where it does stop the inserting of carriage returns (method #1) it sets WrapText (method #2) for all cells its applied to. %MACRO Flow_option(option,suffix); %if "&SUFFIX"eq"" %then %let suffix=&option; ods excel file= "&Folder\wraptext &suffix..xlsx" options( FLOW="&option"); proc odstext; p "Discharges from (screen) and Investigation Records" /style=[tagattr='WrapText:0' fontsize=14pt font_weight=bold]; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners with appropriate HIPAA-compliant business agreements."; p " "; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners wi"; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners with a"; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners with appro"; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners with appropria"; p " "; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners with ap"; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners with app"; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners with appr"; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners with appro"; run; ods excel close; %MEND; /*Examplw with...*/ %Flow_option(DATA); %Flow_option(HEADERS); %Flow_option(ROWHEADERS); %Flow_option(TABLES); %Flow_option(TEXT); %Flow_option(A1:A30,A1toA30); I am thinking about how to put this in a SAS Ballot.

PhilC · ‎05-08-2019

You're right, it is inserting a carriage return but its also setting the WrapText="1" attribute at the same time. I was confused, but I'm still confused the same. So much about ODS makes sense to me, but not the decisions surrounding text wrapping in ODS Excel.

PhilC · ‎05-08-2019

If 139 then is based on the column width then its way off, and too long. The above example uses the default Excel column width and 139 characters fill ~10 times the length of the cell. So I identify two different kinds of line wrapping going on here. First, there is the line wrapping ODS EXCEL implements (fixed with the option Flow), this inserts carriage returns in to the data. Second, the line wrapping done, or not done, because of the specific Excel, XML schema level, tag and attribute <Alignment .. ss:Wraptext=...> set to "1" or "0". But, I think - it seems, that ODS Excel is placing a XML tag and attribute <Alignment .. ss:WrapText="1"> (method #2) conditional on the number of characters. Which, to me, is frustrating ... but not as much as the insertion of carriage returns (Method #1). I really was hoping that tagattr=WrapText would be a supported feature of ODS EXCEL. It's as basic a formatting option as "bold" in my opinion. And thanks for chiming in Cynthia. And thanks for reading to my rants, future and past.

PhilC · ‎05-07-2019

I was trying to output a long string using PROC ODSTEXT, but the lines wrapped in Excel. I tried using "tagattr=wraptext:no", no dice Desperate -- I started varying the length of the string... I found that there was a threshold. ods excel file= "&Folder\wraptext.xlsx"; proc odstext; p "Discharges from (screen) and Investigation Records" /style=[tagattr='WrapText:0' fontsize=14pt font_weight=bold]; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners wi"; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners with a"; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners with appro"; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners with appropria"; p " "; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners with ap"; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners with app"; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners with appr"; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners with appro"; run; ods excel close; The magic number is 139. (???) So why? .

PhilC · ‎05-07-2019

Got a badge for answering my own question. I'm somewhere between "Yoohoo" and "wait -- what's going on here?"

PhilC · ‎05-07-2019

Too slow. Ok I figured this out. I started varying the length of the string and I found that there was a threshold. ods excel file= "&Folder\wraptext.xlsx" options ( Absolute_column_width = ' 140pt, 59pt' ); Title;footnote; proc odstext; p "Discharges from (screen) and Investigation Records" /style=[tagattr='WrapText:0' fontsize=14pt font_weight=bold]; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners wi"; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners with a"; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners with appro"; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners with appropria"; p " "; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners with ap"; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners with app"; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners with appr"; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners with appro"; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners with approp"; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners with appropr"; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners with appropri"; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners with appropria"; run; ods excel close; The magic number 139. (???) So why? I wonder.

PhilC · ‎05-07-2019

Why does the second and third lines wrap, despite using "tagattr=wraptext", while the first one does not wrap? I expect all three lines not to wrap. Do you have any ideas about what I am doing incorrectly? ods excel file= "&Folder\wraptext.xlsx" options ( Absolute_column_width = ' 140pt, 59pt' /*I need these column widths for this to work with a table that I will add after this procedure.*/ ); Title;footnote; proc odstext; p "Discharges from (screen) and Investigation Records" /style=[tagattr='wraptext:no' fontsize=14pt font_weight=bold]; p "This file contains HIPAA protected healthcare information. It is intended for use within this agency and with authorized partners with appropriate HIPAA-compliant business agreements." /style=[tagattr='wraptext:no' ]; p "This file may contain information associated with pre-trial investigations. It is exempt from public record disclosure unless cleared by the appropriate, qualified record custodian." /style=[tagattr='wraptext:no' ]; run; ods excel close; Lets identify two different kinds of line wrapping. First, there is the line wrapping ODS EXCEL implements (fixed with the option Flow) Second, the line wrapping done, or not done, because of the specific Excel, XML schema level, formatting code "WrapText". The latter line wrapping is happening, but I am truly only concerned about the former type of line wrapping. --- SAS Software --- SAS Enterprise Guide version: 7.15 HF7 (7.100.5.6177) SAS System version: 9405.100.22960.39165

PhilC · ‎12-13-2018

After working on these template definitions, for hours, I see ghostly six digit hexadecimal when I blink my eyes. yuck

PhilC · ‎12-13-2018

Thanks @Cynthia_sas, I also knew that I could redefine a style. An earlier attempt left me fruitless and frustrated -- on many levels. This time I pushed through and made myself a "HTMLBW" style. I'm still frustrated because (disclaimer) I know its just a hack and I have no clue if and when I use it that it will randomly fail on me and force me to tweak something. I'm going to share it here, just incase someone might find useful. Styles.HTMLBW: proc template; define style Styles.HTMLBW; parent = styles.statistical; class fonts "Fonts used in the default style" / 'TitleFont2' = ("<sans-serif>, Helvetica, sans-serif",4) 'TitleFont' = ("<sans-serif>, Helvetica, sans-serif",5); class GraphColors / 'gndata12' = cxECE8C4 'gndata11' = cxDBD8F8 'gndata10' = cxC6E4BF 'gndata9' = cxE6CEAD 'gndata8' = cxE5C1D4 'gndata7' = cxCCDFF0 'gndata6' = cxDDDEB5 'gndata5' = cxDBC7E7 'gndata4' = cxD5C6B4 'gndata3' = cxB7D4D3 'gndata2' = cxE7B3B4 'gndata1' = cxBBC2DC 'gndata' = cxBBC2DC 'gofill' = cxFFFFFF 'gout2cend' = cx919191 'gout2cstart' = cxe9e9e9 'gblockheader' = cxcfd5de 'gcphasebox' = cx989EA1 'gphasebox' = cxDBE6F2 'gczonec' = cxBECEE0 'gzonec' = cxCCDCEE 'gczoneb' = cxCCDCEE 'gzoneb' = cxD7E5F3 'gzonea' = cxE3EDF7 'gconramp3cend' = cx9C1C00 'gconramp3cneutral' = cx222222 'gconramp3cstart' = cx0E36AC 'gramp3cend' = cxD05B5B 'gramp3cneutral' = cxFFFFFF 'gramp3cstart' = cx667FA2 'gcontrollim' = cxE6F2FF 'gccontrollim' = cxBFC7D9 'gruntest' = cxCAE3FF 'gcruntest' = cxBF4D4D 'gclipping' = cxFFFFC6 'gcclipping' = cxC1C100 'gaxis' = cx989EA1 'greferencelines' = cx989EA1; class colors / 'link2' = cx0000FF 'link1' = cx800080 'docbg' = cxFFFFFF 'contentbg' = cxFFFFFF 'systitlebg' = cxFFFFFF 'titlebg' = cxFFFFFF 'proctitlebg' = cxFFFFFF 'headerbg' = cxEDF2F9 'captionbg' = cxFFFFFF 'captionfg' = cx000000 'bylinebg' = cxFFFFFF 'notebg' = cxFFFFFF 'tablebg' = cxFFFFFF 'batchbg' = cxFFFFFF 'systitlefg' = cx000000 'titlefg' = cx000000 'proctitlefg' = cx000000 'bylinefg' = cx000000 'notefg' = cx000000; class Header / bordercolor = cx000000 backgroundcolor = cxFFFFFF color = cx000000; class Footer / bordercolor = cx000000 backgroundcolor = cxFFFFFF color = cx000000; class RowHeader / bordercolor = cx000000 backgroundcolor = cxFFFFFF color = cx000000; class RowFooter / bordercolor = cx000000 backgroundcolor = cxFFFFFF color = cx000000; class Table / bordertopwidth = 1px borderleftwidth = 1px borderbottomwidth = 1px borderrightwidth = 1px bordercolor = cx000000 cellpadding = 5; style Data / font = fonts('DocFont') backgroundcolor = cxFFFFFF bordercolor = cx000000 bordertopwidth = 1px borderleftwidth = 1px borderbottomwidth = 1px borderrightwidth = 1px borderstyle = solid; class Graph / attrpriority = "Color"; class GraphFit2 / linestyle = 1; class GraphClipping / markersymbol = "circlefilled"; class Link / color = colors('link2'); class VisitedLink / color = colors('link1'); class ActiveLink / color = colors('link1'); end; run;

PhilC · ‎12-11-2018

How do I get borders on the following example table using the SASdocPrinter style and ODS EXCEL? %MACRO Brdr_clr_wdth(c,w); bordertopcolor=&c bordertopwidth=&w borderbottomcolor=&c borderbottomwidth=&w borderleftcolor=&c borderleftwidth=&w borderrightcolor=&c borderrightwidth=&w %MEND; ODS Excel(CARS) File="%sysfunc(pathname(work,L))\Temp_cars.xlsx" style=SasDocPrinter; proc print data=sashelp.CARS(obs=2) NoObs label style(HEADER)=[font_weight=bold %Brdr_clr_wdth(black,1) ] style(TABLE)=[ %Brdr_clr_wdth(black,1) ] ; VAR Model MSRP; run; ODS Excel(CARS) close;

PhilC · ‎11-28-2018

Ok I figured it out. Removes the header defined by the last line which is a match for "STRING IN THE LAST LINE OF THE HEADER" and the footer defined by the first string which is a match to "STRING IN THE BEGINNING OF THE FOOTER". Its up to the user to find appropriate and unique match strings to make this work. /STRING IN THE LAST LINE OF THE HEADER/h /STRING IN THE BEGINNING OF THE FOOTER/bbeginprint x /STRING IN THE BEGINNING OF THE FOOTER/bprint d :print x p d :beginprint h d Using sed command line: sed -i -n -f "script file_name.sed" "file name.sas" The SED version I am using is GNU SED version 4.2.1, on Windows 10.

PhilC · ‎11-28-2018

I think I've read someone suggest SED script without posting a script. Are there any other ideas? I am familiar with SED, but I'm failing to make it work. I want to process a set of files and remove the line with "GOPTIONS ACCESSIBLE;" and all lines before, as well as the line with "GOPTIONS NOACCESSIBLE;" and all lines after. /* ---------------------------------------- Code exported from SAS Enterprise Guide DATE: Wednesday, November 28, 2018 TIME: 10:33:05 AM /*... blah blah...*/ GOPTIONS ACCESSIBLE; /* ***************************** */ /* My Code, which I want to keep */ /* ***************************** */ %Macro Table_Lookup(command,result); /* etc. etc. */ %mend; GOPTIONS NOACCESSIBLE; %LET _CLIENTTASKLABEL=; /*... blah blah...*/

PhilC · ‎11-28-2018

I respectfully disagree. I referenced the post you responded to in my question, but I'm sure there is more to it. If there is anymore to understand than what I've suggested, I would be interested if anyone knows.

Online Status	Offline
Date Last Visited	‎04-12-2022 11:06 PM

Re: Creating a categorical variable conditioned on a series of if-then...

Re: Creating a categorical variable conditioned on a series of if-then...

Re: Creating a categorical variable conditioned on a series of if-then...

Re: Add Excel File Name as Variable When Importing Multiple Excel File...

Re: How do I format a field using two different formats in proc report

Re: Allow PROC SORT to output multiple datasets

Re: Allow PROC SORT to output multiple datasets

Re: Looking for Like Values in Array

Re: Find Outliers and Matches in dataset

Re: Need help with code, calculating proportion of days covered by med...

Re: How do I format a field using two different formats in proc report

SAS EG 8 scroll bars

Support WITH clauses in SQL

Re: Support WITH clauses in SQL

EG 8 - Bring back the RUN command on the context menu

Re: After upgrading to 8.2, EG could not "find" local host

Re: Option to save the LOG Summary to a dataset

Re: ODS EXCEL: option for the inserting of line feeds when wrapping li...

Re: XML Mapper exporting multiple tables

Re: Looking for Like Values in Array

Re: Data step set or merge question involving lags

Data step set, merge or update question involving lags or retains

Re: PROC ODSTEXT, line wrapping and 139

Re: PROC ODSTEXT, line wrapping and 139

Re: PROC ODSTEXT, line wrapping and 139

PROC ODSTEXT, line wrapping and 139

Re: ODS Excel, PROC ODSTEXT and tagattr=wraptext

Re: ODS Excel, PROC ODSTEXT and tagattr=wraptext

ODS Excel, PROC ODSTEXT and tagattr=wraptext

Re: ODS Excel proc print borders and SasDocPrinter

Re: ODS Excel proc print borders and SasDocPrinter

ODS Excel proc print borders and SasDocPrinter

Re: How can one remove extra EG code from exported code files

How can one remove extra EG code from exported code files

Re: SAS Guide 6.1 ? mark in Query Builder Icon, clarification