I would like extract data from a data base using information in a pre-populated table. The populated table called <required> is attached. Usually, I used to have one row where Processed = 'F'. This time around, the field Processed has two records (both ='F') and I would like to extract two distinct data sets from the database for each value 'F' on the <Processed> table. The database is on <server1> and has two tables sample and result.
I used the following code to extract One data set.
%LET determ = ('cryptosp','giardia', 'MICROCYST','phage 1','phage1', 'CHCl3','CHBrCl2','CHBR3','CHBR2CL','Al', 'As','B','Ba', 'Cd', 'Cl', 'CN', 'CN-Total', 'CN_RECOVERABLE','Colour', 'Conductivity', 'Cr', 'Cu', 'EC', 'F','F/A CL2', 'F_M/CL', 'FC', 'Fe', 'Hardness','Total Hardness','Hg','IC_Cl', 'IC_F', 'IC_NO2', 'IC_NO3', 'IC_SO4', 'Low Hg', 'Mn', 'MONO CL','Na','NH4', 'Ni', 'NO2', 'NO2 AQUA','NO3', 'Odour','Pb', 'pH', 'PHENOL','Sb', 'Se', 'SO4','SPC','HPC', 'Taste','TC','TDS','TDS_CALC','TOC','Total Hardness','Turb','U', 'Zn', 'Ca','Mg','K','Odour','Taste', 'N-Ratio_Calc','TTHM-Ratio')
;
%LET area1 = ('D-BOIT','D-GA_LUKA','D-TOWNLANDS','D-BARN_RES','D-P1_PL', 'D-P2_PL','D-P3_PL','D-P4_PL','D-P5_PL','D-RUST_P6','D-RUST_S_OUT')
;
%LET area2 = ('D-BLOEM_N1','D-BLOEM_N6','D-CLARK_N1','D-CLARK_N4','D-CLARK_N6','D-HEIDEL','D-LESLIE', 'M-A6_SPECIAL','M-A8_SPECIAL','M-A12_SPECIAL','M-A14_SPECIAL','M-A15_SPECIAL', 'M-A19_SPECIAL','M-A20_SPECIAL','M-A21_SPECIAL', 'M-B1_SPECIAL','M-B2_SPECIAL','M-B4_SPECIAL','M-B6_SPECIAL', 'M-B7_SPECIAL','M-B8_SPECIAL','M-B10_SPECIAL','M-B12_SPECIAL','M-B15_SPECIAL');
%LET area3 = ('B-DOM','B-R_COMMUNITY','B-R_POLICE_STA','B-R_SCHOOL');
%LET area4 = ('D-DA15','D-SASOL','D-SASOL_PS','D-SASOL_RES_OUT', 'M-A6_SPECIAL','M-A8_SPECIAL','M-A12_SPECIAL','M-A14_SPECIAL','M-A15_SPECIAL','M-A19_SPECIAL', 'M-A20_SPECIAL','M-A21_SPECIAL', 'M-B1_SPECIAL','M-B2_SPECIAL','M-B4_SPECIAL','M-B6_SPECIAL', 'M-B7_SPECIAL','M-B8_SPECIAL','M-B10_SPECIAL','M-B12_SPECIAL','M-B15_SPECIAL');
A data set was extracted from the database where area = 'AREA1'
data _null_;
file EXECUTE1 temp; set required;
put "proc sql; " / "create table " Report_name " as " / " select sample.sample_number,sample.sampling_point,sample.sampled_date,result.name, result.numeric_entry,result.status, result.reportable"; put " from server1.sample, server1.result" / " where sample.sample_number=result.sample_number and" / " sample.sampled_date between "; put "'" start_date +(-1) "'d and" ; put "'" end_date "'d" ; put "and sample.sampling_point in &area1 and " / " result.name in &determ and " ; put "result.status = 'A' and " / " result.reportable = 'T';" / " quit;";
run; %include EXECUTE1;
/*The different areas in the database has distinct values and these values may change from time to time hence I thought using a small "%Let" macro to represent these values would be handy.*/
Given this, the other data set was extracted using the program below using Area = 'AREA2':
data _null_;
file EXECUTE2 temp; set required;
put "proc sql; " / "create table " Report_name " as " / " select sample.sample_number,sample.sampling_point,sample.sampled_date,result.name, result.numeric_entry,result.status, result.reportable"; put " from server1.sample, server1.result" / " where sample.sample_number=result.sample_number and" / " sample.sampled_date between "; put "'" start_date +(-1) "'d and" ; put "'" end_date "'d" ; put "and sample.sampling_point in &area2 and " / " result.name in &determ and " ; put "result.status = 'A' and " / " result.reportable = 'T';" / " quit;";
run; %include EXECUTE2;
In trying to extract the two tables at the same time, I thought of using a macro as follows:
%MACRO DATA_DATA; proc sql; create table Areas as select area from required;quit; %if Areas.area = 'area1' %then %do; data _null_;
file EXECUTE1 temp; set required;
put "proc sql; " / "create table " Report_name " as " / " select sample.sample_number,sample.sampling_point,sample.sampled_date,result.name, result.numeric_entry,result.status, result.reportable"; put " from server1.sample, server1.result" / " where sample.sample_number=result.sample_number and" / " sample.sampled_date between "; put "'" start_date +(-1) "'d and" ; put "'" end_date "'d" ; put "and sample.sampling_point in &area1 and " / " result.name in &determ and " ; put "result.status = 'A' and " / " result.reportable = 'T';" / " quit;";
run; %END;%else
%if Areas.area = 'area2' %then %do; data _null_;
file EXECUTE2 temp; set required;
put "proc sql; " / "create table " Report_name " as " / " select sample.sample_number,sample.sampling_point,sample.sampled_date,result.name, result.numeric_entry,result.status, result.reportable"; put " from server1.sample, server1.result" / " where sample.sample_number=result.sample_number and" / " sample.sampled_date between "; put "'" start_date +(-1) "'d and" ; put "'" end_date "'d" ; put "and sample.sampling_point in &area2 and " / " result.name in &determ and " ; put "result.status = 'A' and " / " result.reportable = 'T';" / " quit;";
run; %END;
%MEND DATA_DATA; %DATA_DATA;%include EXECUTE1;%include EXECUTE2;
Unfortunately, the macro gives me REP001 data and REP015 data which are the same.
... View more
