Hi there, I would like to ask for assistance on how to  write the SAS code to count number of variables with values. I have the following variables in my table : Diagnosis1,Diagnosis2, Diagnosis3, Diagnosis4, Diagnosis5 And I would like to count the number of variables only if they have a value. For example, if I only have values for Diagnosis1 , Diagnosis2 and Diagnosis3 , and there are NO values for Diagnosis4 and Diagnosis5  , my diagnosis count will be  = 3 . Greatly appreciate any assistance. Thank you in advance.       ... View more

Hi, I would like to ask for assistance regarding SQL Pass-Through facility in SAS EG. I have  never used this function in the past, and would like to inquire on how to retrieve the query results and be able to save as SAS dataset so I can use in data step task. I was able to successfully connect to our DBMS using the sample proc sql statement I created below, however, when I tried to create a SAS dataset from the query results , i am getting an error ,saying that the table I created from the pass -through query is not found. Appreciate any assistance. Thank you in advance.   proc sql; connect to Oracle as mydata(path= path connection = global connection_group ="Con_Group" user = 'myuser' password='mypass'); create table Query_Results as select * from Connection to mydata (select *  from Oracle_MBR_Table where mbr_group_id ='12345'; disconnect from mydata; quit;   data want; set Query_Results; run;     ... View more

Good Morning,   I am working on pulling data for our vendor and it requires me to retrieve claims data  from a very huge dataset. I am pulling claims data from a dataset with 2.5B observations.  I am retrieving all claims records associated with a set of unique claims_case_numbers with approx. 11.5M observations. The resulting dataset is about 52M obs with 47 variables and runtime is 1.5 hrs. I would like to know if my coding approach  below  utilizing Hash object is already efficient for this task or if it can still be improved. Appreciate any suggestions. Also, any recommendations on how to get the dataset results with  approx 13GB in size to our vendor . My initial thought is exporting in csv with zip. Thank you as always.   data vendor_claims; length clm_case_key 8.;   if _N_ =1 then do; DECLARE HASH H(dataset:'CLMS_CASES_LKUP'); H.DEFINEKEY ('clm_case_key');  H.DEFINEDONE(); end;   SET CLAIMS_MASTER (keep=&ON_FILE_HEADER where=((serv_from_date ge '1Jan2016'd AND serv_from_Date le '31Dec2017'd) and prod_cd not in ('PROD_COM','PROD_MDR') and payment_cd = 'C')); if H.find() = 0 then output; run;   ... View more

Good Morning,   What is more efficient and faster , attaching my WHERE clause to the SET statement or within the DATA step, which is what I am currently using ? I reading from a dataset using a hash table . Appreciate any advice. Thanks     data USI_CLAIMS_DATA;   length CPT_CODE $5.; if _N_ =1 then do; DECLARE HASH H(dataset:'USI_CPTCODES'); H.DEFINEKEY ('CPT_CODE'); H.DEFINEDONE(); end;   SET CLAIMS_MASTER(keep=&claims_header); where serv_from_date ge '1Jan2014'd;   if H.find(key:hcpcs_cpt4_base_cd1) = 0 then output; run; ... View more