topic How do you format an expression in an advanced expression -computed column in query builder to calculate percentage? in SAS Enterprise Guide

How do you format an expression in an advanced expression -computed column in query builder to calculate percentage?

Aalcorn — Tue, 29 Oct 2013 19:45:51 GMT

VERSION 4.3

So i am pretty sure the language here is wrong, remember i am new to this, any kind help would be appreciated!
if I select a field from one of my tables/another field ex: (t1.NCOVD_CHRG_AMT/t1.TOT_CHRG)

i get error

1 ;*';*";*/;quit;run;

2 OPTIONS PAGENO=MIN;

3 OPTION DEBUG=DBMS_SELECT SQL_IP_TRACE=(NOTE, SOURCE);

4 PROC SQL NOEXEC;

5 SELECT (t1.NCOVD_CHRG_AMT/t1.TOT_CHRG) AS CALCULATION

6 FROM TABLE1.MEDA_ADJUD_CLM_HDR t1, TABLE2.MEDA_ADJUD_CLM_RSN t2, TABLE3.MEDA_ADJUD_REVN_LN t3

7 QUIT;

______

ERROR 22-322: Syntax error, expecting one of the following: ',', GROUP, HAVING, ORDER, WHERE.

ERROR 76-322: Syntax error, statementw ill be ignored.

8 %PUT SQL IPTRACE_RESULT=&SYS_SQL_IP_ALL;

SQL_IPTRACE_RESULT=-1

9 OPTIONS SQL_IP_TRACE=(NONE);

11 QUIT;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

11 ! RUN;

????????? What would i need to use for the advanced expression to receive a percentage without an error

- PROC SQL NOEXEC;

SELECT (t1.NCOVD_CHRG_AMT/t1.TOT_CHRG) AS CALCULATION

FROM TABLE1.MEDA_ADJUD_CLM_HDR t1, TABLE2.MEDA_ADJUD_CLM_RSN t2, TABLE3.MEDA_ADJUD_REVN_LN t3

QUIT

Re: How do you format an expression in an advanced expression -computed column in query builder to calculate percentage?

Reeza — Tue, 29 Oct 2013 20:36:37 GMT

Are you using Query Builder or writing your own code?

You have 3 tables provided as source tables, are you actually looking for information from 3 tables or only 1?

If you were writing code it would be something like the following, assuming 1 table is the source.

PROC SQL ;

CREATE TABLE WANT AS

SELECT t1. *, (t1.NCOVD_CHRG_AMT/t1.TOT_CHRG) AS PCT_Change format=percent8.2

FROM TABLE1.MEDA_ADJUD_CLM_HDR t1;

QUIT;

Re: How do you format an expression in an advanced expression -computed column in query builder to calculate percentage?

Marina — Tue, 29 Oct 2013 20:52:29 GMT

Are you pulling some data from tables t2 and t3?

If you don't, you need to remove them from FROM clause and add semicolon (;) after t1 in FROM clause - i.e. at the end of SELECT statement.

If you do, you need to tell query how to connect them - add WHERE clause:

WHERE t1.some_field = t2.some_field

and t2.some_field = t3.some_field

Re: How do you format an expression in an advanced expression -computed column in query builder to calculate percentage?

Aalcorn — Thu, 31 Oct 2013 15:19:16 GMT

I am using query builder, and I am using information from one table only, i want to calculate an error rate, noncovere/total charges.

gosh i feel dumb in here!

Re: How do you format an expression in an advanced expression -computed column in query builder to calculate percentage?

asdfghjkl__lpoi87654ewasxcv — Thu, 31 Oct 2013 15:49:55 GMT

The error being displayed is due to a missing semicolon after the select statement and before the quit statement. Suggest adding the ';' and reposting if you get a non-spurious error.

Re: How do you format an expression in an advanced expression -computed column in query builder to calculate percentage?

Aalcorn — Fri, 01 Nov 2013 15:00:42 GMT

I was able to fix the problem, a coworker was available to assist, thank you for everyone's help!