DATA Step, Macro, Functions and more

SAS macro variable with many datetime values

Reply
New Contributor
Posts: 2

SAS macro variable with many datetime values

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;

PROC Star
Posts: 1,547

Re: SAS macro variable with many datetime values

Posted in reply to aleksi555

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

Super User
Posts: 13,283

Re: SAS macro variable with many datetime values

Posted in reply to aleksi555

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

New Contributor
Posts: 2

Re: SAS macro variable with many datetime values

Posted in reply to aleksi555
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.
Contributor
Posts: 36

Re: SAS macro variable with many datetime values

[ Edited ]
Posted in reply to aleksi555

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

Date1Date2dateNEnddate1Enddate2EnddateN
21/05/201822/05/2018 23/05/201824/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;

Ask a Question
Discussion stats
  • 4 replies
  • 74 views
  • 0 likes
  • 4 in conversation