topic Re: Uppercase Excel Output in ODS and Base Reporting

Uppercase Excel Output

PSU_Sudzi — Wed, 04 Sep 2019 14:57:59 GMT

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;

Re: Uppercase Excel Output

Jagadishkatam — Wed, 04 Sep 2019 15:06:27 GMT

You can use the compute block to upcase the variables as below , this is just an example


proc report data=cm_studyname_ready style(column header)=[textalign=left verticalalign=top]
style(Header)=[background=#FFCC99]
style(header)=[font_weight=bold height=.4in]
;
column company ;
define company / computed;
compute company;
company=upcase(compute);
endcomp; 
run;

Re: Uppercase Excel Output

PaigeMiller — Wed, 04 Sep 2019 15:06:40 GMT

You can change the values of the variable in data set CM_STUDYNAME_READY to uppercase in a data step before running PROC REPORT. Use the UPCASE function.

Re: Uppercase Excel Output

Reeza — Wed, 04 Sep 2019 15:08:58 GMT

There's an upcase format.

Apply the formats in a prior step. If you have variables that are numeric and have formats applied you'll need a different approach.

format _character_ $upcase.;

Re: Uppercase Excel Output

Tom — Wed, 04 Sep 2019 15:11:38 GMT

Your code snippet shows PROC REPORT. Do you really need to make a "report" or are you just dumping the data into a spreadsheet?

Do you need Excel or could you produce a simple text file, like a CSV file?

It might be easier to post process the output. That would be really easy for a text file.

filename csv temp;
data _null_;
  file csv dsd ;
  set sashelp.class ;
  put (_all_) (+0) @;
  _file_=upcase(_file_);
  put;
run;

690   data _null_;
691     infile csv ;
692     input;
693     put _infile_;
694   run;

NOTE: The infile CSV is:

      Filename=...\#LN00073,
      RECFM=V,LRECL=32767,File Size (bytes)=403,
      Last Modified=04Sep2019:11:10:06,
      Create Time=04Sep2019:11:10:06

ALFRED,M,14,69,112.5
ALICE,F,13,56.5,84
BARBARA,F,13,65.3,98
CAROL,F,14,62.8,102.5
HENRY,M,14,63.5,102.5
JAMES,M,12,57.3,83
JANE,F,12,59.8,84.5
JANET,F,15,62.5,112.5
JEFFREY,M,13,62.5,84
JOHN,M,12,59,99.5
JOYCE,F,11,51.3,50.5
JUDY,F,14,64.3,90
LOUISE,F,12,56.3,77
MARY,F,15,66.5,112
PHILIP,M,16,72,150
ROBERT,M,12,64.8,128
RONALD,M,15,67,133
THOMAS,M,11,57.5,85
WILLIAM,M,15,66.5,112
NOTE: 19 records were read from the infile CSV.
      The minimum record length was 17.
      The maximum record length was 21.

Re: Uppercase Excel Output

Tom — Wed, 04 Sep 2019 15:18:34 GMT

If your formats are simple 1='High' 2='Low' type formats then you could use PROC FORMAT to convert the formats to a dataset. Upcase the labels and then create new formats.

proc format lib=mylib.myformats cntlout=formats; run;
data formats ;
 set formats;
 label=upcase(label);
run;
proc format lib=work.upcase_formats cntlin=formats; run;
options insert=(fmtsearch=(work.upcase_formats));