SAS Enterprise Guide 7.1 Query Builder Advanced Expression

jimmansfield — Tue, 17 Sep 2019 09:25:05 GMT

Can you help me create the right query builder please?

I have 30MM rows and trying to summarize data at a geographic level and summing a row to column.

Keep returning erros on division and calculating.

Please help.

Thankyou

Expression:

(1-t1.PRODUCTGROUP'SUGAMMADEX'/t1.PRODUCTGROUP'ROCURONIUM BROMIDE')*100

1 The SAS System 15:29 Monday, September 16, 2019

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

2 OPTIONS PAGENO=MIN;

3 %LET _CLIENTTASKLABEL='BRICKindices';

4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';

5 %LET _CLIENTPROJECTPATH='C:\Users\mansfjam\OneDrive - Merck Sharp & Dohme, Corp\Documents\BRIDION\BRIDION2019Sep12.egp';

6 %LET _CLIENTPROJECTPATHHOST='VDESKTOP1850142';

7 %LET _CLIENTPROJECTNAME='BRIDION2019Sep12.egp';

9 ODS _ALL_ CLOSE;

10 OPTIONS DEV=PNG;

11 GOPTIONS XPIXELS=0 YPIXELS=0;

12 FILENAME EGSR TEMP;

13 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR

14 STYLE=HtmlBlue

15 STYLESHEET=(URL="file:///C:/Program%20Files/SAS94/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")

16 NOGTITLE

17 NOGFOOTNOTE

18 GPATH=&sasworklocation

19 ENCODING=UTF8

20 options(rolap="on")

21 ;

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR

23 GOPTIONS ACCESSIBLE;

24 %_eg_conditional_dropds(IQ1KEY.Brick16);

26 PROC SQL;

27 CREATE TABLE IQ1KEY.Brick16 AS

28 SELECT t1.Brick,

29 t1.DSPNSD_UNITS_NBR,

30 /* SUGindex */

31 ((1-t1.PRODUCTGROUP'SUGAMMADEX'/t1.PRODUCTGROUP'ROCURONIUM BROMIDE')*100) FORMAT=BEST13. LABEL="SUGindex"

____________

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,

CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

32 AS SUGindex

33 FROM WORK.QUERY_FOR_BRICKSALESSHARES t1

34 WHERE t1.MAT_DESC = 'MAT 08/2018-07/2019';

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

35 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.01 seconds

37 GOPTIONS NOACCESSIBLE;

40 %LET _CLIENTTASKLABEL=;

41 %LET _CLIENTPROCESSFLOWNAME=;

2 The SAS System 15:29 Monday, September 16, 2019

42 %LET _CLIENTPROJECTPATH=;

43 %LET _CLIENTPROJECTPATHHOST=;

44 %LET _CLIENTPROJECTNAME=;

46 ;*';*";*/;quit;run;

47 ODS _ALL_ CLOSE;

50 QUIT; RUN;

NOW having a sort issue too... any suggestions?

1 The SAS System 15:29 Monday, September 16, 2019

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Query Builder (4)';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='C:\Users\mansfjam\OneDrive - Merck Sharp & Dohme, Corp\Documents\BRIDION\BRIDION2019Sep12.egp';
6 %LET _CLIENTPROJECTPATHHOST='VDESKTOP1850142';
7 %LET _CLIENTPROJECTNAME='BRIDION2019Sep12.egp';
8
9 ODS _ALL_ CLOSE;
10 OPTIONS DEV=PNG;
11 GOPTIONS XPIXELS=0 YPIXELS=0;
12 FILENAME EGSR TEMP;
13 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
14 STYLE=HtmlBlue
15 STYLESHEET=(URL="file:///C:/Program%20Files/SAS94/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
16 NOGTITLE
17 NOGFOOTNOTE
18 GPATH=&sasworklocation
19 ENCODING=UTF8
20 options(rolap="on")
21 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
22
23 GOPTIONS ACCESSIBLE;
24 %_eg_conditional_dropds(WORK.QUERY_FOR_BRICKSALESSHARES1_0000);
25
26 PROC SQL;
27 CREATE TABLE WORK.QUERY_FOR_BRICKSALESSHARES1_0000 AS
28 SELECT t1.Brick,
29 /* ROCunits */
30 (CASE
31 WHEN 'ROCURONIUM BROMIDE'
32 THEN sum(t1.DSPNSD_UNITS_NBR)
33 ELSE 0
34 END) FORMAT=BESTX12. LABEL="ROCunits" AS ROCunits
35 FROM WORK.QUERY_FOR_BRICKSALESSHARES t1
36 GROUP BY t1.Brick;
ERROR: Sort execution failure.

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):
real time 31.67 seconds
cpu time 11.70 seconds

38
39 GOPTIONS NOACCESSIBLE;
40
41
42 %LET _CLIENTTASKLABEL=;
43 %LET _CLIENTPROCESSFLOWNAME=;
44 %LET _CLIENTPROJECTPATH=;
45 %LET _CLIENTPROJECTPATHHOST=;
46 %LET _CLIENTPROJECTNAME=;
47
48 ;*';*";*/;quit;run;
49 ODS _ALL_ CLOSE;
2 The SAS System 15:29 Monday, September 16, 2019

50
51
52 QUIT; RUN;
53

DATA:

GEO PRODUCT UNITS

Abdeen/Iness/Kwall/Lwick MULTI 3873296 3873296
Abdeen/Iness/Kwall/Lwick NEOSTIGMINE 1206609 1206609
Abdeen/Iness/Kwall/Lwick ROCURONIUM BROMIDE 1524626 1524626
Abdeen/Iness/Kwall/Lwick SUGAMMADEX 293834 293834
Bath/Brist/Dchester/Extr/Taunt MULTI 1876989 1876989
Bath/Brist/Dchester/Extr/Taunt NEOSTIGMINE 612252 612252
Bath/Brist/Dchester/Extr/Taunt ROCURONIUM BROMIDE 426775 426775
Bath/Brist/Dchester/Extr/Taunt SUGAMMADEX 109634 109634
Bath/Brist/Dchester/Gcester/Swdn MULTI 1981954 1981954
Bath/Brist/Dchester/Gcester/Swdn NEOSTIGMINE 968322 968322
Bath/Brist/Dchester/Gcester/Swdn ROCURONIUM BROMIDE 1104585 1104585
Bath/Brist/Dchester/Gcester/Swdn SUGAMMADEX 162263 162263
Bburn/Bolt/Prest/Warrton/Wigan MULTI 2042237 2042237
Bburn/Bolt/Prest/Warrton/Wigan NEOSTIGMINE 475402 475402
Bburn/Bolt/Prest/Warrton/Wigan ROCURONIUM BROMIDE 486159 486159
Bburn/Bolt/Prest/Warrton/Wigan SUGAMMADEX 100828 100828

Re: SAS Enterprise Guide 7.1 Query Builder Advanced Expression

brookeewhite1 — Tue, 17 Sep 2019 18:36:08 GMT

I have seen similar errors in my own projects when the dataset or field names had problems, like containing a space, or when the quotation marks indicating a literal were not quite right. I'm not certain that's what you are seeing, it's just the first thought that popped to mind. Good luck!

Re: SAS Enterprise Guide 7.1 Query Builder Advanced Expression

TomKari — Tue, 17 Sep 2019 19:14:50 GMT

On your first log item, I don't think that

t1.PRODUCTGROUP'SUGAMMADEX'

and
t1.PRODUCTGROUP'ROCURONIUM BROMIDE'

is valid SAS syntax. What are you trying to specify? Do the variable names actually have the single quotes in them?

Tom

Re: SAS Enterprise Guide 7.1 Query Builder Advanced Expression

jimmansfield — Wed, 18 Sep 2019 23:52:33 GMT

Thank you.

Approached in two ways:

Query builder > computed columns > advanced expression

then did proc summary and proc transpose to aggregate.

Finally worked nad onto next task.

Thank you for more input.

Re: SAS Enterprise Guide 7.1 Query Builder Advanced Expression

jimmansfield — Wed, 18 Sep 2019 23:53:03 GMT

Thank you.

Approached in two ways:

Query builder > computed columns > advanced expression

then did proc summary and proc transpose to aggregate.

Finally worked nad onto next task.

naming was an issue on length... exporte dhte data to excel; limited the names then reperformed the process.

Thank you for more input.

topic Re: SAS Enterprise Guide 7.1 Query Builder Advanced Expression in SAS Enterprise Guide

SAS Enterprise Guide 7.1 Query Builder Advanced Expression

Re: SAS Enterprise Guide 7.1 Query Builder Advanced Expression

Re: SAS Enterprise Guide 7.1 Query Builder Advanced Expression

Re: SAS Enterprise Guide 7.1 Query Builder Advanced Expression

Re: SAS Enterprise Guide 7.1 Query Builder Advanced Expression