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.
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.