Can I format Excel output from an sql procedure?
Something like
Proc sql;
select count(x) as count_num style={tagattr='format:#,##0'}
from y;
quit;
@Batman wrote:
It gives me an error message
30 ods excel file="S:\HC\private\Temp\temp.xlsx"
31 options (embedded_titles='yes' embedded_footnotes='yes' sheet_name="Info"
32 sheet_interval='none');
33NOTE: The data set WORK.Y has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.25 seconds
cpu time 0.11 seconds
34 Proc sql;35 select count(x) as count_num style={tagattr='format:#,##0'}
_____
22
76
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ',', AS, FORMAT, FROM, INFORMAT, INTO, LABEL, LEN,
LENGTH, TRANSCODE.ERROR 76-322: Syntax error, statement will be ignored.
36 from y;
There's your answer. STYLE= in SQL is not allowed.
Try it an see.
You would have to make sure that the output was directed to an ODS EXCEL file to see.
It gives me an error message
30 ods excel file="S:\HC\private\Temp\temp.xlsx"
31 options (embedded_titles='yes' embedded_footnotes='yes' sheet_name="Info"
32 sheet_interval='none');
33
NOTE: The data set WORK.Y has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.25 seconds
cpu time 0.11 seconds
34 Proc sql;
35 select count(x) as count_num style={tagattr='format:#,##0'}
_____
22
76
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ',', AS, FORMAT, FROM, INFORMAT, INTO, LABEL, LEN,
LENGTH, TRANSCODE.
ERROR 76-322: Syntax error, statement will be ignored.
36 from y;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
37 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
2 The SAS System 19:20 Wednesday, March 1, 2023
real time 0.00 seconds
cpu time 0.00 seconds
38
39 ods excel close;
NOTE: Writing EXCEL file: S:\HC\private\Temp\temp.xlsx
I don't think STYLE is a valid option on a SELECT statement. PROC SQL isn't designed to do any more than simple reporting.
Just add a PROC REPORT or similar reporting PROC after your PROC SQL step creates the required table to report on.
@Batman wrote:
It gives me an error message
30 ods excel file="S:\HC\private\Temp\temp.xlsx"
31 options (embedded_titles='yes' embedded_footnotes='yes' sheet_name="Info"
32 sheet_interval='none');
33NOTE: The data set WORK.Y has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.25 seconds
cpu time 0.11 seconds
34 Proc sql;35 select count(x) as count_num style={tagattr='format:#,##0'}
_____
22
76
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ',', AS, FORMAT, FROM, INFORMAT, INTO, LABEL, LEN,
LENGTH, TRANSCODE.ERROR 76-322: Syntax error, statement will be ignored.
36 from y;
There's your answer. STYLE= in SQL is not allowed.
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.