topic Re: Need help to apply multiple formats based on variable values in PROC REPORT, ODS TAGSETS in ODS and Base Reporting

Need help to apply multiple formats based on variable values in PROC REPORT, ODS TAGSETS

Banu — Sun, 28 Jan 2018 09:52:21 GMT

Hi, I have an issue while applying multiple formats based on the variable values. I saw previous posts, but it was not helpful for me. Mistake might be from my end while applying proper formats. Please help me how to resolve this issue.

INPUT DATA:

============

TYPE TOTALS

A 39442499

B 828727452.170335

B 519125592.249982

B 1491576104.14236

B 31241133938.0128

C 2026005

C 2949321.99999995

REQUIRED OUTPUT:

================

TYPE TOTALS

A 39,442,499

B $8,28,727,452.17

B $ 519,125,592.25

B $1,491,576,104.14

B $31,241,133,938.01

C 2,026,005

C 2,949,322

I tried the code as below but I am getting missing values under totals variable in excel sheet.

ods tagsets.excelxp file='C:\Test\test.xls' style=sasweb;

proc report data=sum nowd;

Column TYPE TOTALS;

define TYPE/ display style(column)={just=l};

define TOTALS/computed style(column)={just=l};

compute TOTALS;

if x='B' then do;

*call define(_col_,'format','dollar19.2');

call define(_col_,'style','style={tagattr="Format:$#,##0.00"}');

end;

else do;

*call define(_col_,'format','comma12.');

call define(_col_,'style','style={tagattr="Format:#,###"}');

end;

endcomp;

run;

ods _all_ close;

PLEASE HELP ME ON THIS. THANKS IN ADVANCE.

Re: Need help to apply multiple formats based on variable values in PROC REPORT, ODS TAGSETS

Cynthia_sas — Sun, 28 Jan 2018 13:46:10 GMT

Hi, your IF statement is testing X, it should be testing TYPE. Also, COMPUTED is the WRONG usage for TOTALS. Unless I misunderstand your data, it looks like your 2 variables are TYPE and TOTALS. Therefore, TOTALS is not new. You only use COMPUTED usage for a completely NEW variable. You can have a COMPUTE block on an existing variable. Please see page 9-10 of this paper http://support.sas.com/resources/papers/proceedings16/SAS5762-2016.pdf for a directly relevant example.

cynthia

Re: Need help to apply multiple formats based on variable values in PROC REPORT, ODS TAGSETS

Tom — Sun, 28 Jan 2018 19:00:38 GMT

As @Cynthia_sas noted you should get a note in the LOG about trying to COMPUTE a variable with the same name as a variable on the input dataset.

NOTE: The computed variable TOTALS is also a data set variable.
NOTE: The output might not be as expected.

Making that change and referencing the TYPE variable your code should work.

data have ;
  input TYPE $ TOTALS ;
cards;
A 39442499
B 828727452.170335
B 519125592.249982
B 1491576104.14236
B 31241133938.0128
C 2026005
C 2949321.99999995
;

%let path=%sysfunc(pathname(work));
ods excel file="&path/test.xlsx";

proc report data=have nowd;
  column TYPE TOTALS;
  define TYPE/ display style(column)={just=l} width=4;
  define TOTALS/ display style(column)={just=l} width=19;
  compute TOTALS;
    if type='B' then do;
       call define(_col_,'format','dollar19.2');
       call define(_col_,'style','style={tagattr="Format:$#,##0.00"}');
    end;
    else do;
       call define(_col_,'format','comma12.');
       call define(_col_,'style','style={tagattr="Format:#,###"}');
    end;
  endcomp;
run;

ods excel close;

  TYPE               TOTALS
  A              39,442,499
  B         $828,727,452.17
  B         $519,125,592.25
  B       $1,491,576,104.14
  B      $31,241,133,938.01
  C               2,026,005
  C               2,949,322

Re: Need help to apply multiple formats based on variable values in PROC REPORT, ODS TAGSETS

Banu — Mon, 29 Jan 2018 10:30:53 GMT

Had few mistakes in my code and then corrected. Now it's working as expected. Thanks for your help.

Re: Need help to apply multiple formats based on variable values in PROC REPORT, ODS TAGSETS

Banu — Mon, 29 Jan 2018 10:31:48 GMT

Cynthia, thank you so much for your suggestion. Attached doc is really helpful for me. Thank you.