SAS Enterprise Guide 7.1 Query Builder Advanced Expression

[jimmansfield]

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';

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(IQ1KEY.Brick16);

25        

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"

                                          ____________

                                          22

                                          76

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

36         

37         GOPTIONS NOACCESSIBLE;

38        

39        

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=;

45         

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

47         ODS _ALL_ CLOSE;

48        

49        

50         QUIT; RUN;

51        

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

[brookeewhite1]

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!

[jimmansfield]

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.

[TomKari]

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

[jimmansfield]

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.