create a marco using two distinct dates from a table to use any where in the programme

[mmohotsi]

I am trying to extract dat using two dates from the attached table. the program is as follows:

proc sql;
create table Date_July_start as
select July as July1 from date1
where 'Milestone No.'n = 1;
select July1 into :Monthbegin from Date_July_start;
quit;

%let Monthbegin=%sysfunc(inputn(&Monthbegin,date9.));

proc sql;
create table Date_July_end as
select July as July2 from date1
where 'Milestone No.'n = 18;
select July2 into :Monthend from Date_July_end;
quit;

%let Monthend=%sysfunc(inputn(&Monthend,date9.));

In finally extracting data, the data has no records. But if I replace for an example &Monthend with a hardcoded date in a date9. format then data shows expected records.

proc sql;
create table test2 as
select sample.parent_aliquot, sample.sampling_point,sample.sampled_date,
result.name, result.numeric_entry,result.status
from <database>
where sample.sample_number = result.sample_number
and sampled_date between &Monthbegin and &Monthend
and sample.sampling_point = 'M-Build'
;
run;

[Quentin]

Hi,

Not enough information for me to debug, since I don't download files.  But I can suggest an approach for you investigate.

You say:

In finally extracting data, the data has no records. But if I replace for an example &Monthend with a hardcoded date in a date9. format then data shows expected records.

proc sql;
create table test2 as
select sample.parent_aliquot, sample.sampling_point,sample.sampled_date,
result.name, result.numeric_entry,result.status
from <database>
where sample.sample_number = result.sample_number
and sampled_date between &Monthbegin and &Monthend
and sample.sampling_point = 'M-Build'
;
run;

I would suggest you write the code with hardcoded values for &Monthbegin and &Monthend and see that it works.  And keep that step for reference.

Then look at the values of the macro variables Monthbegin and Monthend and make sure they are exactly the same values as you have in your hardcoded step.  You can look at the values with:

%put &=MonthBegin &=MonthEnd;

And also turn on options symbolgen.  I would add that immediately before the query.  You should see a difference there, which should point to an answer.  Remember, the macro language is just text substitution.

If that doesn't help, please run both the hard-coded query that works, and the code with the macro variable references that does not work (with the %PUT statement added), and post the log.

Glancing at your code, unless your dates are character, I don't think it could be working.  Could be that when you hardcode your are adding quotes to make a date-literal, i.e. "10Jan2023"d and that the values of the macro variables may be only 10Jan2023.  Also, be sure to check your log for errors/warnings that will help you debug.

[Kurt_Bremser]

If your dates are already SAS date values, the intermediate %LETs with the INPUTN will not work. Omit them.

[ErikLund_Jensen]

Hi @mmohotsi 

The variable July in your input table is a SAS date with format date9.

So all you need to create both macro variables is:

proc sql noprint;
  select 
    min(July), max(July)
    into: Monthbegin, :Monthend
  from tmp2.date1;
quit;

Result: 

45 %put &=Monthbegin &=Monthend;
MONTHBEGIN= 22848 MONTHEND= 22872

[Tom]

---

@mmohotsi wrote:

...

 But if I replace for an example &Monthend with a hardcoded date in a date9. format then data shows expected records.

...

and sampled_date between &Monthbegin and &Monthend

 If you replaced the macro variables with string like you claim then you will have invalid syntax like:

sampled_date between 01JUL2022 and 01SEP2022

Since text like 01JUL2022 is neither the name of a variable nor a valid number nor a valid string.

Your code is instead trying to generate code like:

sampled_date between 22827 and ...

Which will work fine since the number 22,827 is the value SAS uses to represent the date 01JUL2022.

So please show the code that works and the types (numeric or character) and any display formats that are attached to the variables you are use.   

This is important since it might be that SAMPLED_DATE is actually a DATETIME value, in spite of the variable name.  In which case a number like 22,827 would represent some time early on 01JAN1960 since there are 86,400 seconds in a day.

[mmohotsi]

The following codes yields results

proc sql;
create table test2 as
select sample.parent_aliquot, sample.sampling_point,sample.sampled_date,
result.name, result.numeric_entry,result.status
from <database>
where sample.sample_number = result.sample_number
and sampled_date between &Monthbegin and '19Oct2022'd
and sample.sampling_point = 'M-Buid'

;
quit;

proc sql;
create table test2 as
select sample.parent_aliquot, sample.sampling_point,sample.sampled_date,
result.name, result.numeric_entry,result.status
from <database>
where sample.sample_number = result.sample_number
and sampled_date between '29Jul2022'd and &Monthbegin
and sample.sampling_point = 'M-Buid'

;
quit;

So, If I use any ONE macro at a time then the results are obtained. The problem is when I use both the macros

[Tom · Accepted solution]

So if those two queries work as expected then it means you have set the macro VARIABLE named MONTHBEGIN to a valid date value.  So I suspect the issue is that the other date you are trying to put into the MONTHEND variable is not valid.  What date did you expect that to be? What value did the MONTHEND macro variable end up containing?

From your original code you have set the macro variable to contain the digit string that represents the raw number of days value that SAS uses to store dates.  So if the date was 01JUL2022 then the raw date value would be 22827.  That would be easier if you just did that when you first create the macro variable by telling PROC SQL what format to use to display the value when it created the macro variable.

select July1 format=10. into :Monthbegin trimmed from Date_July_start;

You could also have set MONTHBEGIN to a value like "01JUL2022"d so that it looks like date literals you used in the code that works.

select cats('"',put(July1,date9.),'"d') into :Monthbegin from Date_July_start;

Or you could have set it to just 01JUL2022

select July1 format=date9. into :Monthbegin from Date_July_start;

and then added the quotes and the D when you used the macro variable to generate code.

sampled_date between "&Monthbegin"d