The SAS Output Delivery System and reporting techniques

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

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

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

[ Edited ]

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.


Accepted Solutions
Solution
‎01-29-2018 05:29 AM
Super User
Super User
Posts: 8,287

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

[ Edited ]

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

image.png

View solution in original post


All Replies
SAS Super FREQ
Posts: 9,434

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

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
Contributor
Posts: 44

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

Posted in reply to Cynthia_sas
Cynthia, thank you so much for your suggestion. Attached doc is really helpful for me. Thank you.
Solution
‎01-29-2018 05:29 AM
Super User
Super User
Posts: 8,287

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

[ Edited ]

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

image.png

Contributor
Posts: 44

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

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

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 355 views
  • 0 likes
  • 3 in conversation