Is it possible to set more than one datetime values into %let-function? Below is query which works but there are only one "date" and one "endingdate". Is it possible to have list of two or more datetimes and get result for each "pair" into one table?
let% date= '1apr2015:00:00:00.001'DT;
let% endingdate= '31mar2018:00:00:00.001'DT;
;
proc sql;
create table test as
select
variable1, &date.,&endingdate.
from table1
where
date1 >= &date.
and date1 <= &endingdate.
;
run;
Sure possible by concatenating with a delimiter. What is your expected output or requirement. Samples of your input and wanted output makes it easy for responders to give you a precise solution. Thanks
@aleksi555 wrote:
Is it possible to set more than one datetime values into %let-function? Below is query which works but there are only one "date" and one "endingdate". Is it possible to have list of two or more datetimes and get result for each "pair" into one table?
let% date= '1apr2015:00:00:00.001'DT;
let% endingdate= '31mar2018:00:00:00.001'DT;
;
proc sql;
create table test as
select
variable1, &date.,&endingdate.
from table1
where
date1 >= &date.
and date1 <= &endingdate.
;
run;
You should provide some example data of the input and the desired output.
If you have multiple values of anything in a single macro variable you will not be able to use statements such as
date1 >= &date.
but would have to build code to the proper compound comparison for multiple values. Without knowing what you might mean with that comparison I'm not going to attempt to guess at this point. Equal comparisons are easier as you can use an IN operator but there is not equivalent for < or > comparisons.
As a minimum you might have to delve in to macro programing to build the proper comparisons.
Better might be to build a data set, join it and compare that way. But again without a more concrete example of input and desired result it is hard to be more specific.
What ever information we have so far I can suggest following,
-Create DATE Array
Then
-Iterate using loop.
Hope that helps
Date dataset should b like that to create an array
Date1 | Date2 | dateN | Enddate1 | Enddate2 | EnddateN |
21/05/2018 | 22/05/2018 | 23/05/2018 | 24/05/2018 |
Here is a sample code. you have to make changes as per your dataset
array date[n];
array enddate[n];
Do i=1 to n;
proc sql;
create table test as
select variable1, &date.,&endingdate.
from table1
where
date[n] >= &date.
and date[n] <= &endingdate.
;quit;
end;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.