Hi, Does anyone have any experience on running Proc sql on very large databases (say healthcare claims DB having 500m-1b records)? Any references to papers will also work. And any advantage if the DB is teradata? SAS is at 9.1.3 I am familiar with hash joins (used it once maybe) but am not a technical person to understand the theory behind it. Any guidance would be appreciated. Thanks, saspert. ... View more

Hi, I have SAS EG at my office and the version is is 9.1.3. The unix version is AIX 64 (according to the proc setinit log). When I try _NOLOG_ and _PASSWORD_ options with macro variables, neither of them seem to work. Anyone with a positive experience with these options? Please let me know. Thanks, saspert. ... View more

Hi, I have a scenario where my proc sqls generate 0 records. But there are several steps that are  dependant on this resultant datasets like transpose and sql and print. Is there a way to manage blank datasets in a away that there are no errors thrown because of missing fields? Thanks, saspert. ... View more

Hi, I have a stored process code in the following format - *ProcessBody; %include "/sasdev/.../sas_parms.sas"; LIBNAME TEST ORACLE  PATH=&SASLIBPATH SCHEMA=&SASLIBSCHEMA USER=&SASLIBUSERID password="&SASLIBPW"; %include '/sasdev/.../excltags.sas'; %macro _createpage; .....put statements writing javascript code put '<form name="my_form"'; put ' method="post"'; put ' action="http://sasdev.myclient.com:8080/SASStoredProcess/do" >'; put '<input type="hidden"'; put ' name="_program"'; put ' value="//Foundation/BIP Tree/ReportStudio/Shared/Reports/StoredProcesses/..../completion_weekly">'; .....put statements writing javascript code %mend _createpage; %macro output; %let _ODSDEST=tagsets.ExcelXP; /*%let _DEBUG=log,trace; When using this... the %let RV= statements need to be commented out which will redirect the output to a HTML window rather than MS Excel*/ %let RV=%sysfunc(stpsrv_header(Content-type, application/vnd.ms-excel)); %let RV=%sysfunc(stpsrv_header(Content-disposition, attachment;filename="Boeing_HA_Completion_Weekly.xls"));  /***************************NOW START ADDING CODE HERE ********************************/ %STPBEGIN; ods tagsets.ExcelXP file=_webout style=sasweb options(doc="help"); ods tagsets.ExcelXP options(sheet_name='Male Students' sheet_interval='none');  proc print data=sashelp.class;     where (sex eq 'M');  run; quit;  proc print data=sashelp.class;     where (age eq 15);  run; quit;  ods tagsets.ExcelXP options(sheet_name='Female Students' sheet_interval='none');  proc print data=sashelp.class;    where (sex eq 'F');  run; quit; proc print data=sashelp.class;     where (age eq 14);  run; quit;  ods tagsets.ExcelXP close; %STPEND; /***************************NOW FINISH ADDING CODE HERE ********************************/ data _null_; file _webout; put '<center><a href="http://sasdev.uhc.com:8080/SASStoredProcess/do?&_program=//Foundation/BIP Tree/ReportStudio/Shared/Reports/StoredProcesses/Optum/rpt/phi/shr_stp/ha_completion_weekly"> <img src="http://unitedteams.uhc.com/SCS/URN/OSSA/Pictures/SAS%20Store%20Process%20Images/Prompt.bmp" border="0" /> </a></center>'; run; %mend output; %macro main; %if %symexist(refresh_submit) %then %do; %if &refresh_submit ne 1 %then   %do;   %create_page;   %end; %else    %do;   %output;   %end; %end; %else %do; %create_page; %end; %mend; %main; When I run this code with the _debug=log,trace , I am getting an xml errror The XML page cannot be displayed Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later. -------------------------------------------------------------------------------- Only one top level element is allowed in an XML document. Error processing resource 'http://sasdev.uhc.com:8080/SASStoredPr... <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" -^ AN> ss:ID="systemtitle5__c" ss:Parent="systemtitle5">   <Alignment ss:WrapText="1" ss:Horizontal="Center" /> Any help would be appreciate. Thanks, saspert. ... View more

Hi, I have a coding issue related to PROC SQL. In the code below. PURCHASER and CUSTOMER are two macro variables whose values are populated by a javascript drop down values which allow only 1 value to be selected. Example: PURCHASER: NJ State, TX State, PA State... CUSTOMER: Dept of labor, Dept of Commerce.... The new filter I am trying to add has a default value of All but is also a drop down menu where 1 value can be selected. The new code is in red below CSATREPID: All. Jim, John, Joe.... PROC SQL;    connect to oracle as myconn (user=&ORAID password=&ORAPW path=&ORAPATH );   CREATE TABLE HIST_ADT AS SELECT  *    from connection to myconn (SELECT DISTINCT     PRCHSR_CD AS Purchaser_Code     ,CUST_LEG_NM AS Customer_Short_Name     ,FST_NM AS First_Name     ,MIDL_NM AS Middle_Name     ,LST_NM AS Last_Name .... FROM    D_MEMBER D_MEMBER INNER JOIN  F_CSAT_OPER F_CSAT_OPER INNER JOIN  D_CSAT_OPER D_CSAT_OPER    .... WHERE  (D_CUSTOMER.PRCHSR_CD = %str(%')&PURCHASER.%str(%') AND D_CUSTOMER.CUST_LEG_NM = %str(%')&CUSTOMER.%str(%') .... AND ( D_CSAT_OPER.CSR_USER_NM like %str(%')&CSATREPID.%str(%')     OR D_CSAT_OPER.CSR_USER_NM IN (%oracleQuote(parm=CSATREPID)) ) AND ...) quit; %macro oracleQuote(parm=); %if %symexist(&parm.0) %then %do;    %do i = 1 %to &&&parm.0;        %if &i = 1 %then   %do;     %str(%')&&&parm&i%str(%')            %end;             %else %do;       %str(,%str(%')&&&parm&i%str(%'))                 %end;        %end;        %end;         %else %do;   %str(%')&&&parm%str(%') %end; %mend; The issue is - if I select All, the PROC SQL returns 0 records because there are no values for 'All' but in fact should return all records matching Jim, Joe, John et al. Is there a way to covert 'All' option to default to everything? Thanks, saspert ... View more