DATA Step, Macro, Functions and more

PERCENT format in SQL table query

Reply
Occasional Contributor
Posts: 5

PERCENT format in SQL table query

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.
Super User
Posts: 19,822

Re: PERCENT format in SQL table query

<calculation as percentage> as my_percent_variable format=percent8.2,
Occasional Contributor
Posts: 5

Re: PERCENT format in SQL table query

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.
Super User
Posts: 19,822

Re: PERCENT format in SQL table query

[ Edited ]

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.

 

Occasional Contributor
Posts: 5

Re: PERCENT format in SQL table query

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.
Super User
Posts: 19,822

Re: PERCENT format in SQL table query

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. 

 

 

Ask a Question
Discussion stats
  • 5 replies
  • 92 views
  • 0 likes
  • 2 in conversation