BookmarkSubscribeRSS Feed
greg6363
Obsidian | Level 7
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.
5 REPLIES 5
Reeza
Super User
<calculation as percentage> as my_percent_variable format=percent8.2,
greg6363
Obsidian | Level 7
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.
Reeza
Super User

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.

 

greg6363
Obsidian | Level 7
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.
Reeza
Super User

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. 

 

 

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 20795 views
  • 0 likes
  • 2 in conversation