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
... View more