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

[Banu]

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.

[Cynthia_sas]

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

[Banu]

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

[Tom · Accepted solution]

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

[Banu]

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