Fetching dates from an external table for use in Proc SQL

[mmohotsi]

I am trying to extract data from a sql database using proc sql. Am only pointing to two tables sample and result in the database. The usual programme I use is as follows:

proc sql;

create table data1 as

select sample.numbered, result.value,sample.sampled_date, sample.area,result.name

from employer.sample, employer.result

where sample.numbered=result.numbered

and sample.sampled_date between 21jan2020'd and '30jan2020'd

and sample.area in ('a','b')

and result.name = 'Colour';

quit;

The challenge now is that the dates from the customer is provided in a table form in a sql table and I now have to replace the sampled_date values in the programme above with the contents of the table as provided by the  customer without manually typing the dates from the customer and then run separate reports for each customer separately. The table below shows a table from the business.

customer	start_date	end_date
customer1	21 Jan 20	30 Jan 20
customer2	18 Feb 20	03 Mar 20
customer3	13 Feb 20	22 Feb 20

[MCoopmans · Accepted solution]

Hello,

Since you want to run three seperate queries, I suggest you generate the code that you need to be executed with a datastep. You could either write the code to a file that you could then %INCLUDE to execute it, or use CALL EXECUTE() to store them in a temporary buffer and they wil execute after your datastep terminates.

Here is an example of writing the code to a temporary :

filename torun temp;

data want;
infile datalines;
input customer : $10. start_date : $7. end_date : $7. ;
datalines;
customer1 21Jan20 30Jan20
customer2 18Feb20 03Mar20
customer3 13Feb20 22Feb20
;
run;


data _null_;
 file torun;
  set want;
  put "******   QUERY FOR CUSTOMER : " customer "    ******;";
  put "proc sql; " / "create table " customer " as" / "  select sample.numbered, result.value, sample.sampled_date, sample.area,result.name";
  put " from employer.sample, employer.result" / " where sample.numbered=result.numbered and" / " sample.sampled_date between ";
  put "'"  start_date +(-1) "'d and" ;
  put "'"  end_date   +(-1) "'d" ;
  put " and sample.area in ('a','b')" / " and result.name = 'Colour';" / " quit;";
run;

* %include torun;

After running this you could open a new program editor and run this command in the command line box "inc torun" that will include that temporary file and you can check the generated code. If it looks fine you could simply uncomment the last line and the generated code will be execute upon submit.

[mmohotsi]

Dear Mathias Coopmans

Thank you very much. Solution accepted and worked perfectly
Regards
MMohotsi