topic Re: using tagattr with sql in ODS and Base Reporting

using tagattr with sql

Batman — Wed, 01 Mar 2023 22:20:26 GMT

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;

Re: using tagattr with sql

ballardw — Wed, 01 Mar 2023 23:37:37 GMT

Try it an see.

You would have to make sure that the output was directed to an ODS EXCEL file to see.

Re: using tagattr with sql

Reeza — Wed, 01 Mar 2023 23:45:52 GMT

I'd also recommend just trying a comma format and seeing if that worked 🙂

Re: using tagattr with sql

Batman — Thu, 02 Mar 2023 00:24:08 GMT

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

Re: using tagattr with sql

SASKiwi — Thu, 02 Mar 2023 00:40:12 GMT

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.

Re: using tagattr with sql

PaigeMiller — Thu, 02 Mar 2023 11:30:49 GMT

@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');
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;

There's your answer. STYLE= in SQL is not allowed.