About PSU_Sudzi

PSU_Sudzi · ‎09-04-2019

I am outputting a dataset into Excel and am trying to get all of the values within each cell to be uppercase. The problem is that some variables are codelisted and those decode values are not uppercase, e.g. 'Not related'. I am stuck working with the format catalog with those mixed case codelists and tried running an array to change them but it seems that is only upcasing the value in the variables and not the codelisted value which I suppose makes sense. Is there a style/option within ODS that I can use to set everything to be uppercase? *opens excel output; ods _all_ close; ods excel file= "YOUR FILE PATH HERE.xlsx" options( sheet_interval="none" sheet_name="&sysdate." frozen_headers="1" autofilter='on' ); options nolabel; options missing='.'; proc report data=cm_studyname_ready style(column header)=[textalign=left verticalalign=top] style(Header)=[background=#FFCC99] style(header)=[font_weight=bold height=.4in] ; run; ods excel close; *opens destination again - choose the ones you need; ods html; ods listing;

PSU_Sudzi · ‎08-26-2019

These have all been great tips and I am able to get the output I'm looking for, thank you all!!!

PSU_Sudzi · ‎08-23-2019

Thank you Cynthia, I will review this when I am back in the office on Monday.

PSU_Sudzi · ‎08-23-2019

Thank you Andreas_lds. What I am looking for is to have all of the values/text in the cells align to the left and top of the cell, as well as the headers. I think by default when you send something to Excel it seems to align the cell text/values to the right and bottom. Basically I'm trying to use ODS to customize my output into Excel so I no longer need to open the file and apply filters, alignments, etc. by hand. I will try your suggestion also when I am back in the office on Monday.

PSU_Sudzi · ‎08-22-2019

Thanks Reeza! If I could get the title to print across the top row of the Excel that would be cool. Does the 'title j=l' only align the title left or the whole excel file, column headers and values, that's what I'm looking to do?

PSU_Sudzi · ‎08-22-2019

I am trying to left and top justify the Excel output but am not sure what options/code would do it. Any suggestions on what to add to this code? Bonus points if there is a way to suppress the output/results window and just dump this all right to Excel. ods excel file= "path to file here.xlsx" options( sheet_interval="none" sheet_name="&sysdate." frozen_headers="1" autofilter='on' ); proc report data=sample_dataset; title "Your Title Here"; run; ods excel close;

PSU_Sudzi · ‎08-20-2019

Thank you Novinosrin, this also seems like it would work, will give it a shot too!

PSU_Sudzi · ‎08-20-2019

Thanks for the response Reeza. In this dataset I only need to account for missing days, not months or years as I might in others. I hadn't thought of converting the -- to 01 and then using INTNX, but that seems like a good idea!

PSU_Sudzi · ‎08-20-2019

Hi Folks, I have a piece of code that I use (below) to default missing days from partial dates to the first of the month when appropriate but in this case I need to default the missing days to the end of the month, which seems more complicated to a novice like me since the last day of the month can vary. Any ideas how to proceed? /* month and year entered, default to start of month */ if substr(eoslvdat,1,1) = '-' and substr(eoslvdat,3,1) ne '-' then eoslvdtn=input(trim('01')||substr(eoslvdat,3),date9.) data WORK.EOS; infile datalines dsd truncover; input _usubjid:$6. eoslvdat:$9.; label _usubjid="Unique Subject Identifier" eoslvdat="Date of last visit"; datalines; 01/001 25APR2019 01/002 02MAY2019 01/003 08OCT2018 01/004 15NOV2018 01/005 12JUN2018 01/006 06JUN2019 01/007 --DEC2018 ;;;;

PSU_Sudzi · ‎07-22-2019

Below are the proc formats as well as the code from the instructions on how to post data steps here. Note that I was unable to get data with values from the data step below until I removed the infile line of code. PROC FORMAT ; VALUE CL_NA 0 = 'Applicable' 1 = 'Not Applicable' ; VALUE CL_PWA 0 = 'Clear' 1 = 'Near Clear' 2 = 'Thin' 3 = 'Thick' ; data WORK.WART_GRADE_TEST; infile datalines dsd truncover; input _usubjid:$6. visnum:32. _visname:$16. date:DATE9. _recnum:32. tlidia:32. tlithk:32. tlina:CL_NA. plares:CL_PWA. plana:CL_NA.; format date DATE9. tlina CL_NA. plares CL_PWA. plana CL_NA.; label _usubjid="Unique Subject Identifier" visnum="Visit Number" _visname="Visit Name" date="Date" _recnum="Wart" tlidia="Lesion diameter (mm)" tlithk="Lesion Thickness" tlina="Lesion not identified" plares="PLA Grading" plana="Lesion not identified"; datalines; 01/001 10 VISIT01 31JAN2019 1 4 1 . Thin . 01/001 10 VISIT01 31JAN2019 2 3 1 Applicable Thin Applicable 01/001 10 VISIT01 31JAN2019 3 . . Not Applicable . Not Applicable 01/001 10 VISIT01 31JAN2019 4 . . Not Applicable . Not Applicable 01/001 10 VISIT01 31JAN2019 5 . . Not Applicable . Not Applicable 01/001 10 VISIT01 31JAN2019 6 . . Not Applicable . Not Applicable 01/001 20 VISIT02 07FEB2019 1 4 1 . Thin . 01/001 20 VISIT02 07FEB2019 2 3 1 Applicable Thin Applicable 01/001 20 VISIT02 07FEB2019 3 . . Not Applicable . Not Applicable 01/001 20 VISIT02 07FEB2019 4 . . Not Applicable . Not Applicable 01/001 20 VISIT02 07FEB2019 5 . . Not Applicable . Not Applicable 01/001 20 VISIT02 07FEB2019 6 . . Not Applicable . Not Applicable 01/001 30 VISIT03 14FEB2019 1 3 . . Thin . 01/001 30 VISIT03 14FEB2019 2 3 . Applicable Thin Applicable 01/001 30 VISIT03 14FEB2019 3 . . Not Applicable . Not Applicable 01/001 30 VISIT03 14FEB2019 4 . . Not Applicable . Not Applicable 01/001 30 VISIT03 14FEB2019 5 . . Not Applicable . Not Applicable 01/001 30 VISIT03 14FEB2019 6 . . Not Applicable . Not Applicable 01/001 40 VISIT04 21FEB2019 1 3 . . Thin . 01/001 40 VISIT04 21FEB2019 2 2 . Applicable Near Clear Applicable 01/001 40 VISIT04 21FEB2019 3 . . Not Applicable . Not Applicable 01/001 40 VISIT04 21FEB2019 4 . . Not Applicable . Not Applicable 01/001 40 VISIT04 21FEB2019 5 . . Not Applicable . Not Applicable 01/001 40 VISIT04 21FEB2019 6 . . Not Applicable . Not Applicable 01/001 50 VISIT05 28FEB2019 1 3 . . Thin . 01/001 50 VISIT05 28FEB2019 2 0 . Applicable Clear Applicable 01/001 50 VISIT05 28FEB2019 3 . . Not Applicable . Not Applicable 01/001 50 VISIT05 28FEB2019 4 . . Not Applicable . Not Applicable 01/001 50 VISIT05 28FEB2019 5 . . Not Applicable . Not Applicable 01/001 50 VISIT05 28FEB2019 6 . . Not Applicable . Not Applicable 01/001 60 VISIT06 07MAR2019 1 3 . . Thin . 01/001 60 VISIT06 07MAR2019 2 . . Not Applicable Clear Applicable 01/001 60 VISIT06 07MAR2019 3 . . Not Applicable . Not Applicable 01/001 60 VISIT06 07MAR2019 4 . . Not Applicable . Not Applicable 01/001 60 VISIT06 07MAR2019 5 . . Not Applicable . Not Applicable 01/001 60 VISIT06 07MAR2019 6 . . Not Applicable . Not Applicable ;;;;

PSU_Sudzi · ‎07-22-2019

Working on it now.

PSU_Sudzi · ‎07-22-2019

Thank you for your prompt response earlier and pointing me in the direction of posting a data step here, I was actually looking for that post and have now bookmarked it. So as far as output goes, I am looking to see a list of those records that meet the criteria mentioned, i.e. the TLIDIA is missing at a subsequent visit but is present at earlier visit for the same subject/record number. In the dataset below there is one record that meets that criteria (01/001 60 VISIT06 07MAR2019 2 . . Not Applicable Clear Applicable). data WORK.WART_GRADE; infile datalines dsd truncover; input _usubjid:$6. visnum:32. _visname:$16. date:DATE9. _recnum:32. tlidia:32. tlithk:32. tlina:CL_NA. plares:CL_PWA. plana:CL_NA.; format date DATE9. tlina CL_NA. plares CL_PWA. plana CL_NA.; label _usubjid="Unique Subject Identifier" visnum="Visit Number" _visname="Visit Name" date="Date" _recnum="Wart" tlidia="Lesion diameter (mm)" tlithk="Lesion Thickness" tlina="Lesion not identified" plares="PLA Grading" plana="Lesion not identified"; datalines; 01/001 10 VISIT01 31JAN2019 1 4 1 . Thin . 01/001 10 VISIT01 31JAN2019 2 3 1 Applicable Thin Applicable 01/001 10 VISIT01 31JAN2019 3 . . Not Applicable . Not Applicable 01/001 10 VISIT01 31JAN2019 4 . . Not Applicable . Not Applicable 01/001 10 VISIT01 31JAN2019 5 . . Not Applicable . Not Applicable 01/001 10 VISIT01 31JAN2019 6 . . Not Applicable . Not Applicable 01/001 20 VISIT02 07FEB2019 1 4 1 . Thin . 01/001 20 VISIT02 07FEB2019 2 3 1 Applicable Thin Applicable 01/001 20 VISIT02 07FEB2019 3 . . Not Applicable . Not Applicable 01/001 20 VISIT02 07FEB2019 4 . . Not Applicable . Not Applicable 01/001 20 VISIT02 07FEB2019 5 . . Not Applicable . Not Applicable 01/001 20 VISIT02 07FEB2019 6 . . Not Applicable . Not Applicable 01/001 30 VISIT03 14FEB2019 1 3 . . Thin . 01/001 30 VISIT03 14FEB2019 2 3 . Applicable Thin Applicable 01/001 30 VISIT03 14FEB2019 3 . . Not Applicable . Not Applicable 01/001 30 VISIT03 14FEB2019 4 . . Not Applicable . Not Applicable 01/001 30 VISIT03 14FEB2019 5 . . Not Applicable . Not Applicable 01/001 30 VISIT03 14FEB2019 6 . . Not Applicable . Not Applicable 01/001 40 VISIT04 21FEB2019 1 3 . . Thin . 01/001 40 VISIT04 21FEB2019 2 2 . Applicable Near Clear Applicable 01/001 40 VISIT04 21FEB2019 3 . . Not Applicable . Not Applicable 01/001 40 VISIT04 21FEB2019 4 . . Not Applicable . Not Applicable 01/001 40 VISIT04 21FEB2019 5 . . Not Applicable . Not Applicable 01/001 40 VISIT04 21FEB2019 6 . . Not Applicable . Not Applicable 01/001 50 VISIT05 28FEB2019 1 3 . . Thin . 01/001 50 VISIT05 28FEB2019 2 0 . Applicable Clear Applicable 01/001 50 VISIT05 28FEB2019 3 . . Not Applicable . Not Applicable 01/001 50 VISIT05 28FEB2019 4 . . Not Applicable . Not Applicable 01/001 50 VISIT05 28FEB2019 5 . . Not Applicable . Not Applicable 01/001 50 VISIT05 28FEB2019 6 . . Not Applicable . Not Applicable 01/001 60 VISIT06 07MAR2019 1 3 . . Thin . 01/001 60 VISIT06 07MAR2019 2 . . Not Applicable Clear Applicable 01/001 60 VISIT06 07MAR2019 3 . . Not Applicable . Not Applicable 01/001 60 VISIT06 07MAR2019 4 . . Not Applicable . Not Applicable 01/001 60 VISIT06 07MAR2019 5 . . Not Applicable . Not Applicable 01/001 60 VISIT06 07MAR2019 6 . . Not Applicable . Not Applicable

PSU_Sudzi · ‎07-22-2019

I have a dataset with a layout as in the table below and I am trying to find a way to check if values are missing for TLIDIA (longest diameter) at subsequent visits when they are present at Visit 1. For example, since _RECNUM (lesions) 1 and 2 each have a measurement, those same lesions should have a TLIDIA present throughout the study. Same with PLARES (grade of lesion). In the table below there aren't any records that meet that criteria. Any idea how to get this started with a datastep or proc sql? Many thanks! _usubjid visnum _visname date _recnum tlidia tlithk tlina plares plana 01/001 10 VISIT01 31-Jan-19 1 4 1 2 01/001 10 VISIT01 31-Jan-19 2 3 1 0 2 0 01/001 10 VISIT01 31-Jan-19 3 1 1 01/001 10 VISIT01 31-Jan-19 4 1 1 01/001 10 VISIT01 31-Jan-19 5 1 1 01/001 10 VISIT01 31-Jan-19 6 1 1 01/001 20 VISIT02 7-Feb-19 1 4 1 2 01/001 20 VISIT02 7-Feb-19 2 3 1 0 2 0 01/001 20 VISIT02 7-Feb-19 3 1 1 01/001 20 VISIT02 7-Feb-19 4 1 1 01/001 20 VISIT02 7-Feb-19 5 1 1 01/001 20 VISIT02 7-Feb-19 6 1 1 01/001 30 VISIT03 14-Feb-19 1 3 2 01/001 30 VISIT03 14-Feb-19 2 3 0 2 0 01/001 30 VISIT03 14-Feb-19 3 1 1 01/001 30 VISIT03 14-Feb-19 4 1 1 01/001 30 VISIT03 14-Feb-19 5 1 1 01/001 30 VISIT03 14-Feb-19 6 1 1

PSU_Sudzi · ‎07-02-2019

Thanks Reeza, this is for my own use but also will be exporting things for folks to review so decoding them at the beginning seems the best approach rather than doing it piecemeal along the way for exports.

PSU_Sudzi · ‎07-02-2019

OK, thanks for the tip. Would you add these new variables at the end of the import for each dataset, then rename them back and drop the old variable?

Online Status	Offline
Date Last Visited	‎09-12-2019 07:11 PM

Uppercase Excel Output

Re: ODS Excel, Left and Top Justify Output

Re: ODS Excel, Left and Top Justify Output

Re: ODS Excel, Left and Top Justify Output

Re: ODS Excel, Left and Top Justify Output

ODS Excel, Left and Top Justify Output

Re: Default date values to end of month

Re: Default date values to end of month

Default date values to end of month

Re: Checking records for missing values at subsequent visits

Re: Uppercase Excel Output

Re: Uppercase Excel Output

Re: Uppercase Excel Output

Re: Uppercase Excel Output

Re: ODS Excel, Left and Top Justify Output

Re: SAS Format Decodes Missing on export to Excel or viewing in SAS vi...

Uppercase Excel Output

Re: ODS Excel, Left and Top Justify Output

Re: ODS Excel, Left and Top Justify Output

Re: ODS Excel, Left and Top Justify Output

Re: ODS Excel, Left and Top Justify Output

ODS Excel, Left and Top Justify Output

Re: Default date values to end of month

Re: Default date values to end of month

Default date values to end of month

Re: Checking records for missing values at subsequent visits

Re: Checking records for missing values at subsequent visits

Re: Checking records for missing values at subsequent visits

Checking records for missing values at subsequent visits

Re: SAS Format Decodes Missing on export to Excel or viewing in SAS vi...

Re: SAS Format Decodes Missing on export to Excel or viewing in SAS vi...