topic Re: SAS macro variable with many datetime values in SAS Programming

SAS macro variable with many datetime values

aleksi555 — Thu, 24 May 2018 14:21:04 GMT

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;

Re: SAS macro variable with many datetime values

novinosrin — Thu, 24 May 2018 14:35:03 GMT

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

Re: SAS macro variable with many datetime values

ballardw — Thu, 24 May 2018 14:41:15 GMT

@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.

Re: SAS macro variable with many datetime values

aleksi555 — Thu, 24 May 2018 14:44:38 GMT

Hi, thanks for your message. I would like the output as follows: consider %let "date" would contain datetimes: date1 and date2.
Also %let "endingdate" contains endingdate1 and endingdate2 values. Now i want the query to run first with values "date1" and "endingdate1" and after that with "date2" and "endingdate2". At the end id have "variable1" values along with these two date combinations all in one table.

Re: SAS macro variable with many datetime values

emrancaan — Thu, 24 May 2018 15:09:12 GMT

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;