I am writing a report to an RTF file using ODS RTF.  I was using PDF, but it was decided that the client wanted RTF.  I am using a PRETEXT statement in PROC REPORT.  However, when I was using ODS PDF, I simply put "~n" to get a new line in the pretext text (where the tilda is my escapechar).  When I switch to RTF, that doesn't seem to work.  I just prints "~n" along with the regular text in a single line.  Is there something different I should be using?  I have tried /line/ and ~{newline}.  Neither seems to work.  Any ideas?   /*****************************************/ /*** Create the SAS log error report ***/ /*****************************************/ ods escapechar='~'; ods listing close; ods rtf file="&outputdir.\ZCHECKLOG.rtf" style=MonoChromePrinter; title1 h=0.5 j=l 'Vertex Pharmaceuticals Incorporated' j=r 'Page ~{thispage}'; proc report data=final_t center nowindows headline headskip spacing=1 split='^' missing style(header)={fontsize=9pt} style(report)={font=('Arial',9pt,bold italic) pretext="SAS Log Report for Directory ~n &logdir. ~n Run Date: &sysdate9."}; column filename cat1 cat2 cat3 cat4 cat5; define filename / order order=data width=45 left "File Name"; define cat1 / display center width=5 "Errors"; define cat2 / display center width=5 "Warnings"; define cat3 / display center width=5 "Uninitialized^Variables"; define cat4 / display center width=5 "Merge^Notes"; define cat5 / display center width=5 "Invalid^Data"; compute cat1; if cat1>0 then call define(_col_,"style","style={background=red}"); endcomp; compute cat2; if cat2>0 then call define(_col_,"style","style={background=red}"); endcomp; compute cat3; if cat3>0 then call define(_col_,"style","style={background=yellow}"); endcomp; compute cat4; if cat4>0 then call define(_col_,"style","style={background=yellow}"); endcomp; compute cat5; if cat5>0 then call define(_col_,"style","style={background=yellow}"); endcomp; run; ods rtf close; ods listing; ... View more

This is more of an Excel question than SAS, but since I'm altering Excel so that I can read in an Excel file, I figured I'd try this wonderful group.   I am given an Excel file from a Data Manager that has a list of subjects and a column for each visit with the table populated with expected visit dates.  There are a lot of values that have been copied from a table with formulas to a raw table, but that means that even though these are all date values, many of the fields have formula results such as 'DOVerror or 'FLC=ND.   When I read this in, SAS looks at the first several rows to determine how a variable should be characterized.  There are many of the visits where SAS correctly recognizes it as a column of dates and makes it a numeric variable with a date format and automatically makes blank those unwanted character strings.  But when there are a bunch of these blank fields in a given column, it thinks they are character strings and then treats the SAS data like a character string.   I know that that apostrophe in front of the text is a way to force Excel to treat the value as a character string.  (So, if I entered "001" and hit enter, Excel would convert it to 1 and treat it as a number.  But if I entered '001, Excel would store it as a character string with 3 characters.)  My initial thought was to just do a find/replace and change all "DOVerror" and "FLC=ND" to missing values, but I noticed that it leaves a blank field, but when you click on a blank cell, it still shows a lonely apostrophe.  I tried to change 'DOVerror to a blank value, but it said it couldn't find any such string.   So, my question is this: How can I quickly replace 'DOVerror or 'FLC=ND as missing values and have it treated like a numeric field instead of a blank character field?   I have attached a sample of my data so you can see in person what I'm talking about.  Hopefully it all makes sense. ... View more

I have a dataset that has a list of visits and dates for each subject along with an anchor visit and a visit window.  I want to create a variable that displays the visit date that matches the anchor visit.  Below is a subset of my data and my method for getting the anchor date.  I can get everything to line up with the Day 1 anchor because Day 1 is found within itself, but I can't get the Day 10 or Day 11 anchors to be detected because there is no match within those anchor subsets.  If you run the code below and open the DOV_REFDATE table, you will see what I mean.  I just need the REF_DATE column to be fully populated.  Any suggestions?   proc sql; create table dov (SUBJID char(11), EVENT char(50), EVENTDT char(10), MIN_WINDOW num, MAX_WINDOW num, ANCHOR char(50)); insert into dov (subjid, event, eventdt, min_window, max_window, anchor) values('001-001-073','Screening', '2019-07-09',-28,-2,'Part B Day 1') values('001-001-073','Day -1', '2019-08-05', -1,-1,'Part B Day 1') values('001-001-073','Part B Day 1', '2019-08-06', 1, 1,'Part B Day 1') values('001-001-073','Randomization', '2019-08-06', 1, 1,'Part B Day 1') values('001-001-073','Part B Day 2', '2019-08-07', 1, 1,'Part B Day 1') values('001-001-073','Part B Day 3', '2019-08-08', 1, 1,'Part B Day 1') values('001-001-073','Part B Day 4', '2019-08-09', 1, 1,'Part B Day 1') values('001-001-073','Part B Day 5', '2019-08-10', 1, 1,'Part B Day 1') values('001-001-073','Part B Day 6', '2019-08-11', 1, 1,'Part B Day 1') values('001-001-073','Part B Day 7', '2019-08-12', 1, 1,'Part B Day 1') values('001-001-073','Part B Day 8', '2019-08-13', 1, 1,'Part B Day 1') values('001-001-073','Part B Day 9', '2019-08-14', 1, 1,'Part B Day 1') values('001-001-073','Part B Day 10', '2019-08-15', 1, 1,'Part B Day 1') values('001-001-073','Part B Day 11', '2019-08-16', 1, 1,'Part B Day 1') values('001-001-073','Part B Day 12', '2019-08-17', 1, 1,'Part B Day 1') values('001-001-073','Part B Day 13', '2019-08-18', 1, 1,'Part B Day 1') values('001-001-073','Part B Day 14', '2019-08-19', 1, 1,'Part B Day 1') values('001-001-073','Part B Safety Follow-up', '2019-08-23', 7,10,'Part B Day 10') values('001-001-081','Screening', '2019-08-29',-28,-2,'Part B Day 1') values('001-001-081','Day -2', '2019-09-18', -2,-2,'Part B Day 1') values('001-001-081','Day -1', '2019-09-19', -1,-1,'Part B Day 1') values('001-001-081','Randomization', '2019-09-19', 1, 1,'Part B Day 1') values('001-001-081','Part B Day 1', '2019-09-20', 1, 1,'Part B Day 1') values('001-001-081','Part B Day 2', '2019-09-21', 1, 1,'Part B Day 1') values('001-001-081','Part B Day 3', '2019-09-22', 1, 1,'Part B Day 1') values('001-001-081','Part B Day 4', '2019-09-23', 1, 1,'Part B Day 1') values('001-001-081','Part B Day 5', '2019-09-24', 1, 1,'Part B Day 1') values('001-001-081','Part B Day 6', '2019-09-25', 1, 1,'Part B Day 1') values('001-001-081','Part B Day 7', '2019-09-26', 1, 1,'Part B Day 1') values('001-001-081','Part B Day 8', '2019-09-27', 1, 1,'Part B Day 1') values('001-001-081','Part B Day 9', '2019-09-28', 1, 1,'Part B Day 1') values('001-001-081','Part B Day 10', '2019-09-29', 1, 1,'Part B Day 1') values('001-001-081','Part B Day 11', '2019-09-30', 1, 1,'Part B Day 1') values('001-001-081','Part B Day 12', '2019-10-01', 1, 1,'Part B Day 1') values('001-001-081','Part B Day 13', '2019-10-02', 1, 1,'Part B Day 1') values('001-001-081','Part B Day 14', '2019-10-03', 1, 1,'Part B Day 1') values('001-001-081','Part B Day 15', '2019-10-04', 1, 1,'Part B Day 1') values('001-001-081','Part B Safety Follow-up', '2019-10-07', 7,10,'Part B Day 11'); quit; proc sql; create table dov_refdate (keep=subjid anchor event eventdt ref_date max_window min_window) as select a.*, b.eventdt as ref_date from dov as a left join (select subjid, event, eventdt from dov where event=anchor) as b on a.subjid=b.subjid & a.anchor=b.event; quit; ... View more

I have done this several times, but I cannot find past code or remember what I did even though I know this is a pretty simple task.  I think I'm just going brain dead and can't think anymore.   I have a table of patient visits and dates (DOV) and another table of visits and anchor visits (WINDOWS) so I can try to find out if a visit is outside of the protocol-specified window.  I cannot think how to identify the anchor date (the EVENTDT that corresponds with the ANCHOR visit).  In the end, I want to see the DOV table with a new variable called ANCHORDT that should have values of 2019-09-05 for Subject 001-001-001 and 2019-09-06 for Subject 001-001-004 (but the Termination visits should not have an anchor date since that is not found in the WINDOWS table).  This is a very simple example, but in real life there could be multiple anchor dates per subject (just depending on what visit is assigned to be the anchor for each following visit).   Here is my sample data:     proc sql; create table dov (SUBJID char(11), EVENT char(50), EVENTDT char(10)); insert into dov (subjid, event, eventdt) values ('001-001-001', 'Screening', '2019-08-20') values ('001-001-001', 'Day -1', '2019-09-04') values ('001-001-001', 'Randomization', '2019-09-04') values ('001-001-001', 'Part A Treatment Period Day 1', '2019-09-05') values ('001-001-001', 'Part A Treatment Period Day 2', '2019-09-06') values ('001-001-001', 'Part A Treatment Period Day 3', '2019-09-07') values ('001-001-001', 'Part A Treatment Period Day 4', '2019-09-08') values ('001-001-001', 'Part A Treatment Period Day 5', '2019-09-09') values ('001-001-001', 'Termination', '2019-09-12') values ('001-001-004', 'Screening', '2019-08-22') values ('001-001-004', 'Day -1', '2019-09-05') values ('001-001-004', 'Randomization', '2019-09-06') values ('001-001-004', 'Part A Treatment Period Day 1', '2019-09-06') values ('001-001-004', 'Part A Treatment Period Day 2', '2019-09-07') values ('001-001-004', 'Part A Treatment Period Day 3', '2019-09-08') values ('001-001-004', 'Part A Treatment Period Day 4', '2019-09-09') values ('001-001-004', 'Part A Treatment Period Day 5', '2019-09-10') values ('001-001-004', 'Termination', '2019-09-13'); create table windows (EVENT char(50), MIN_WINDOW num, MAX_WINDOW num, ANCHOR char(50)); insert into windows (event, min_window, max_window, anchor) values('Screening', -28, -2, 'Part A Treatment Period Day 1') values('Day -1', -1, -1, 'Part A Treatment Period Day 1') values('Randomization', 1, 1, 'Part A Treatment Period Day 1') values('Part A Treatment Period Day 1', 1, 1, 'Part A Treatment Period Day 1') values('Part A Treatment Period Day 2', 2, 2, 'Part A Treatment Period Day 1') values('Part A Treatment Period Day 3', 3, 3, 'Part A Treatment Period Day 1') values('Part A Treatment Period Day 4', 4, 4, 'Part A Treatment Period Day 1') values('Part A Treatment Period Day 5', 5, 5, 'Part A Treatment Period Day 1') values('Part A Safety Follow-up', 7, 10, 'Part A Treatment Period Day 1'); quit;               ... View more

I am trying to create a report that shows a list of subjects, their date/time of first dose, date/time of last dose, and the difference between the two date/times.  I am supposed to report the difference in the format "D Days:HH:MM".  In other words, if the first dose date/time was 01JAN2019:08:00 and the last dose date/time was 05JAN2019:09:30, then the result for the difference would say, "4 Days:01:30".   I have been able to use INTNX to get the number of days, but how would I further extract the hours and minutes that has transpired?  I think my biggest concern is if the example above had the times swapped: 01JAN2019:09:30 and 05JAN2019:08:00.  In this case, the difference should be "3 Days:22:30".  I don't think INTNX would produce the proper number of days.  It would still return 4.   Here is a sample dataset to help get you started.  Any suggestions?   proc sql; create table have (SUBJECT char(11), MINDTTM num format=datetime18., MAXDTTM num format=datetime18.); insert into have (subject, mindttm, maxdttm) values('001-001-001','06DEC2018:07:35:00'dt,'08JAN2019:08:24:00'dt) values('001-001-002','06DEC2018:07:50:00'dt,'07JAN2019:07:17:00'dt) values('001-001-003','07DEC2018:08:30:00'dt,'07JAN2019:07:30:00'dt) values('001-001-004','07DEC2018:08:40:00'dt,'18MAR2019:07:49:00'dt) values('001-001-005','01JAN2019:08:00:00'dt,'05JAN2019:09:30:00'dt) values('001-001-006','01JAN2019:09:30:00'dt,'05JAN2019:08:00:00'dt); quit; data want; set have; daydiff=intck('DTDAY',mindttm,maxdttm); run;   ... View more