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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.