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
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!
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.
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.