I was not aware that was possible in SAS studio. Thanks for writing this page ... View more

Try using   WHERE CU.DEATH_DT is not null ... View more

I could not understand how SQL query being used in SASTABLE  macro but if I only focus on the SQL query it looks okay to me.  You are doing inner join and also applied where filter condition. It seems okay to me. You can check whether DEATH_DT column is numeric in the source table.  ... View more

Very smart use of tabulate. ... View more

I have written code to have better control. I have used two macro m1 and m2 to supply year and month value. m1 would be base year and base month and m2 would be compare year and compare month. The final output would be stored in _Result dataset.   data have; input v_date mmddyy10. v_id $ v_qty; format v_date date9.; datalines; 1/30/2016 US1 20 1/30/2016 US2 34 2/28/2016 US1 10 2/28/2016 US3 7 ; %let m1=201601; %let m2=201602; data _base_mth _cmp_mth; set have; start_month=MOD(&m1,100); end_month=MOD(&m2,100); start_year=int(&m1 / 100); end_year=int(&m2 / 100); t_mth=month(v_date); t_yr=year(v_date); if t_mth = start_month and t_yr= start_year then output _base_mth; if t_mth = end_month and t_yr= end_year then output _cmp_mth; run; proc sort data=_base_mth ; by v_id; run; proc sort data=_cmp_mth ; by v_id; run; data _Result(keep= v_id src_qty cmp_qty status) ; merge _base_mth(in=a rename= v_qty =src_qty) _cmp_mth(in=b rename= v_qty =cmp_qty); by v_id; length status $20; if a and not b then do; status ="Deleted"; output _Result; end; if not a and b then do; status ="Newly Added"; output _Result; end; if a and b then do; if src_qty ne cmp_qty then do; status ="Updated"; output _Result; end; if src_qty eq cmp_qty then do; status ="No change"; output _Result; end; end; run; ... View more

This is the simplest form of command, there are other option that you can also use if needed.   <SAShomepath>/SASHOME/SAS Foundation/9.4/sas.exe -sysin <codepath>/test.sas -log <logpath>/test.log   <SAShomepath> - SAShome path, <codepath> - path where sas code is stored  <logpath> - path where you want to store log file. ... View more

proc sql; create table data1_data2 as select a.Reg_no ,a.collection_date ,b.Ud_COLLECTION_DATE from data1 a left join data2_latest b on a.Reg_no = b.Reg_no ; quit; proc sort data =data1_data2 ; by Reg_No descending collection_date descending Ud_COLLECTION_DATE; run; data data2_latest; set =data1_data2 ; by descending collection_date descending Ud_COLLECTION_DATE; if first.Reg_No and Ud_COLLECTION_DATE > collection_date -86400 then output; run; proc sql; create table data1_data3 as select a.Reg_no ,a.collection_date ,c.Um_COLLECTION_DATE from data1 a left join data3_latest c on a.Reg_no = c.Reg_no ; quit; data data3_latest; set =data1_data3 ; by descending collection_date descending Um_COLLECTION_DATE if first.Reg_No and Um_COLLECTION_DATE> collection_date -86400 then output; run; If you are happy with data2_latest and data3_latest then merge these two dataset  proc sql; create table want as select a.Reg_no ,a.collection_date ,b.Ud_COLLECTION_DATE ,c.Um_COLLECTION_DATE from data1 a left join data2_latest b on a.Reg_no = b.Reg_no left join data3_latest c on a.Reg_no = c.Reg_no ; quit; ... View more

This piece of code would take latest collection date within 24 hrs. In case there are two collection within 24 hours, only latest one would be considered   "first.regno"   data data3_latest; set data3_24hrs; by descending Um_COLLECTION_DATE if first.Reg_No and Um_COLLECTION_DATE> datetime() -86400 then output; run;  datetime() - 86400 would find the collection date within 24 hours. ... View more

I have done step by step In case your final result have data1 with no UD_collection_date and UM_Collection_date then remove those record.    /* Sorting data2 descending order*/ proc sort data=data2 out= data2_24hrs; by descending Ud_COLLECTION_DATE; run; data data2_latest; set data2_24hrs; by descending Ud_COLLECTION_DATE; if first.Reg_No and Ud_COLLECTION_DATE > datetime() -86400 then output; run; /* Sorting data 3 desending order*/ proc sort data=data3 out= data3_24hrs; by descending Um_COLLECTION_DATE run; data data3_latest; set data3_24hrs; by descending Um_COLLECTION_DATE if first.Reg_No and Um_COLLECTION_DATE> datetime() -86400 then output; run; proc sql; create table want as select a.Reg_no ,a.collection_date ,b.Ud_COLLECTION_DATE ,c.Um_COLLECTION_DATE from data1 a left join data2_latest b on a.Reg_no = b.Reg_no left join data3_latest c on a.Reg_no = c.Reg_no ; quit;   ... View more