Extracting data using distinct table records from a pre-populated table

mmohotsi — Mon, 18 Oct 2021 09:50:26 GMT

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;

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.

Re: Extracting data using distinct table records from a pre-populated table

ballardw — Mon, 18 Oct 2021 15:16:28 GMT

What is this line of code supposed to do?

%if Areas.area = 'area1' %then %do;

If you think it is supposed to extract the value of a variable from a data set an use it then that is conceptually wrong.

Macro code does not "see" variables in data sets. You can explicitly make a macro variable from the contents of a data set using the CALL SYMPUTX or CALL SYMPUT functions that the macro processor then can see.

Second thing is to the macro processor everything is text so quite often including quotes around values to compare is incorrect. If the value of a macro variable is area1 then you do not use: %if &somevar = 'area1' because that means the quotes are part of the value instead %if &somevar = area1 %then %do ....

If you want want to do something based on multiple records in a data set then perhaps you should be looking at CALL EXECUTE to create code from the contents of the data set.

BTW for small example data sets a DATA Step that will create the data set is preferred.

Attached SAS data sets mean we have to go through gyrations to move the data around as clicking on the set makes a local different library for us with the data and then we have to make sure that all your references match that location or move the data set.

There are also some potential issues between different operating systems/versions of SAS creating the data set and who may be able to use it such as issue from language encoding. The data step we can all run and have a working data set.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

topic Re: Extracting data using distinct table records from a pre-populated table in SAS Programming

Extracting data using distinct table records from a pre-populated table

Re: Extracting data using distinct table records from a pre-populated table