topic Re: Rename Statement - Columns not renamed in Proc Report ODS output in SAS Programming

Rename Statement - Columns not renamed in Proc Report ODS output

buechler66 — Thu, 02 Apr 2020 17:30:14 GMT

Hi. I'm having trouble with my Rename statement. It runs without error, but in the very next step where I use Proc Report with ODS only the _FREQ_ variable is renamed to FREQ in the output. The other two variables don't appear to get renamed.

Any help would be greatly appreciated.

DATA &SITE._HCR_SUMMARY;
SET &SITE._SUMS_;
IF AREA = '' AND HCRID = '' AND PAY_DATA_MONTH = . AND ACTIVITY_DESCRIPTION = '' THEN ACTIVITY_DESCRIPTION = 'SITE TOTAL'; 
IF AREA NE '' AND HCRID NE '' AND PAY_DATA_MONTH = . AND ACTIVITY_DESCRIPTION = '' THEN ACTIVITY_DESCRIPTION = 'HCR CONTRACT TOTAL'; 
IF AREA NE '' AND HCRID NE '' AND PAY_DATA_MONTH NE . AND ACTIVITY_DESCRIPTION = '' THEN ACTIVITY_DESCRIPTION = 'MONTHLY CONTRACT TOTAL'; 
DROP _TYPE_;
RENAME _FREQ_=FREQ PAY_DATA_MONTH=MONTH_PAY_DATA RATE=RATE_PER_UNIT;
FORMAT PAY_AMOUNT DOLLAR15.2 RATE DOLLAR15.2 UNITS COMMA15.2;
RUN;


/* EXPORT THE FILE TO XLS USING ODS
   NOTE FOR OLDER VERSIONS OF SAS YOU COULD USE ODS TAGSETS.EXCELXP FILE="C:\TEST.XML";
   TO GET THE SAME RESULT 
*/
ODS EXCEL FILE="C:\USERS\STEVEBUECHLER\BOX SYNC\IBM\USPS\HRC PAY DATA ANALYSIS\&SITE._HCR_PD_SUMMARY.XLSX";
PROC REPORT DATA=&SITE._HCR_SUMMARY;
  COLUMNS _ALL_;
RUN;
ODS EXCEL CLOSE;

Re: Rename Statement - Columns not renamed in Proc Report ODS output

Reeza — Thu, 02 Apr 2020 17:38:05 GMT

PROC REPORT is likely defaulting to the labels.

Strip all the labels from the data to force the variable names to show.

proc datasets lib=work memtype=data;
   modify &SITE._HCR_SUMMARY;
     attrib _all_ label=' ';
run;
quit;

Then run your proc REPORT.

I haven't tried it but you could also try adding the

attrib _all_ label='';

line to your data step and see if that removes all the labels.

Re: Rename Statement - Columns not renamed in Proc Report ODS output

buechler66 — Thu, 02 Apr 2020 17:45:34 GMT

Worked like a charm. Thanks so much for taking the time to help!

Re: Rename Statement - Columns not renamed in Proc Report ODS output

ballardw — Thu, 02 Apr 2020 20:13:34 GMT

Another approach:

/* instead of */
RENAME _FREQ_=FREQ PAY_DATA_MONTH=MONTH_PAY_DATA RATE=RATE_PER_UNIT;
/* change the label*/
label _FREQ_="FREQ" 
      PAY_DATA_MONTH="MONTH PAY DATA" 
      RATE="RATE PER UNIT"
;

Note use of slightly nicer text in the label vs variable name.

And who restarted the school of programming everything in capital letters???