topic Re: PERCENT format in SQL table query in SAS Programming

PERCENT format in SQL table query

greg6363 — Thu, 05 Oct 2017 18:06:04 GMT

I have a PROC sql query that is hitting several SQL tables on a SQL server platform. I am trying to format a calculated field as a percentage but when I use the format percent 8.2 function after the declared variable, I get an error message. What SQL code do I need to format the field for this variable?

Any assistance would be greatly appreciated. Thanks.

Re: PERCENT format in SQL table query

Reeza — Thu, 05 Oct 2017 18:38:12 GMT

<calculation as percentage> as my_percent_variable format=percent8.2,

Re: PERCENT format in SQL table query

greg6363 — Thu, 05 Oct 2017 18:49:32 GMT

That's what I used and I get the following error:

CLI describe error: [Microsoft][SQL Server Native Client 11.0][SQL Server] Incorrect syntax near 'format'. : [Microsoft][SQL Server Native Client 11.0][SQL Server] Statement(s) could not be prepared.

I'm not querying on a SAS server. I am querying tables on a SQL server so the SAS language will not work.

What SQL code can be used in SAS to properly format a field that calculates a percentsges?

Thanks.

Re: PERCENT format in SQL table query

Reeza — Thu, 05 Oct 2017 18:51:29 GMT

Ok. Please post the full details of your issue, demo code and log that shows what's not working is a good start.

@greg6363 wrote:
That's what I used and I get the following error:

CLI describe error: [Microsoft][SQL Server Native Client 11.0][SQL Server] Incorrect syntax near 'format'. : [Microsoft][SQL Server Native Client 11.0][SQL Server] Statement(s) could not be prepared.

I'm not querying on a SAS server. I am querying tables on a SQL server so the SAS language will not work.

What SQL code can be used in SAS to properly format a field that calculates a percentsges?

Thanks.

Re: PERCENT format in SQL table query

greg6363 — Thu, 05 Oct 2017 19:17:12 GMT

I put the log details of the error in the last message.

Here's the code:

sum(cash)/sum(originatingamt) as col_rate format=percent8.2

When I run it, I get the error message that I detailed in the previous post.

As I said, I am querying a SQL table, not a SAS table.

Re: PERCENT format in SQL table query

Reeza — Thu, 05 Oct 2017 19:21:56 GMT

If you're using pass through SQL then it needs to be in a format that the server understands. Since you're using MS SQL (T-SQL) see what Microsoft requires. Or format it after you bring it into SAS using PROC DATASETS.