Using date values in the Macros

Reply
Frequent Contributor
Posts: 107

Using date values in the Macros

[ Edited ]

 

 

SAS : 9.3 Enterprise Guide 5.1

Hello all,

 

I am having trouble extracting the data with a where statement that has macro varibles with dates.

 

The main dataset has a varibale RATEEFFECTIVEDATE contains dates in DATETIME22.3 format(ex: 16AUG2015:00:00:000).

 

I have two macro variables coming from prompts(created with prompt manager) called date_min date_max and has fixed values.

 

 dates_min = 16aug2015

 dates_max =18aug2015

 

I am using the below code to extract data with a where statement.

 

data want;
set have;
where (datepart(rateeffectivedate) ge "&dates_min"d) and (datepart(rateeffectivedate) le "&dates_max"d);
run;

 

The above method does not seem to be helping me giving the following error:

 

NOTE: There were 0 observations read from the data set have.
WHERE 0 /* an obviously FALSE WHERE clause */ ;

 

Please can anyone help me out with this. Thanks in advance.

Trusted Advisor
Posts: 1,913

Re: Using date values in the Macros

Posted in reply to KiranMaddi

Well for one thing you data step code uses macro variable &DATES_MIN instead of &DATE_MIN; similar problem for &DATE_MAX

Frequent Contributor
Posts: 107

Re: Using date values in the Macros

Posted in reply to PaigeMiller

Sorry it was just a typo error. 

Super User
Super User
Posts: 7,942

Re: Using date values in the Macros

Posted in reply to KiranMaddi

You probably need to trim the macro %trim():

WHERE DATEPART(RATEEFFECTIVEDATE) GE "%trim(&DATES_MIN)"D) A

 

However its not really a good idea to write code like that.  If you want to advence the calendar, or do computation etc. then it just makes lots of really unecessary code.  Far simpler to create a dataset with the parameters, and then use that:

data params;

  start='01JAN2014'd; end='14JAN2014'd; output;

run;

 

proc sql;   

  create table WANT as

  select  * from HAVE where RATEEFFECTIVEDATE between (select START from PARAMS) and (select END from PARAMS);

quit;

 

Also, why do you need to do this in a macro at all, why does base SAS not provide you the functionality?

 

And please don't code all in caps, makes it really hard to read.

 

Frequent Contributor
Posts: 107

Re: Using date values in the Macros

[ Edited ]

Hi RW9. Thanks for your reply.

 

To make the story short. I have a master dataset resides on the server. I have created a program using prompts so that users can extract data they want from that master dataset. So, in the prompts there is date prompt where user selects from and to dates from the calender in the propmt window. Propmts automatically created the macro variables dates_min and dates_max looks in this format (06apr2015). So I want this date to match the dates in the master dataset and extracts the data to adifferent location. I hope this answers your question.

 

Thank for your suggestion. I will use small letters next time.

Super User
Posts: 5,503

Re: Using date values in the Macros

Posted in reply to KiranMaddi

You do have unbalanced parentheses ... missing a left-hand parenthesis after the word WHERE.

Frequent Contributor
Posts: 107

Re: Using date values in the Macros

Posted in reply to Astounding

Yeah, nice spot. But it is again a typo error. Please see the actual code below copied from the program.

 

(DATEPART(MISC_RATEEFFECTIVEDATE) GE "&DATES_MIN"D) AND (DATEPART(MISC_RATEEFFECTIVEDATE) LE "&DATES_MAX"D)

Super User
Posts: 19,789

Re: Using date values in the Macros

Posted in reply to KiranMaddi

 

DATEPART(MISC_RATEEFFECTIVEDATE) Between "&DATES_MIN"D AND "&DATES_MAX"D;

Try BETWEEN instead.

 

 

If that doesn't work, please post a proc contents of your have dataset, perhaps your 

MISC_RATEEFFECTIVEDATE

variable is actually a character?

 

 

Frequent Contributor
Posts: 107

Re: Using date values in the Macros

Hi Reeza,

 

Thanks for your email. 

 

Between did not work. Still the same error as before. Please see the variable properties below.

 

Capture.JPG

Trusted Advisor
Posts: 1,913

Re: Using date values in the Macros

Posted in reply to KiranMaddi

Okay, @KiranMaddi, stop making us guess what your code is and what error you are getting.

 

Show us the full code, and relevant parts of the SASLOG where it shows the error.

Frequent Contributor
Posts: 107

Re: Using date values in the Macros

[ Edited ]
Posted in reply to PaigeMiller

 

Hi,

Here is the code and log

 

data &id..aquotedata ;
set aquotes.multibrand (keep = misc_a_qte_key--credit_publictime_zd &keep_list sold_miscaffinityparent--sold_vehiclemarkup);
where (datepart(misc_rateeffectivedate) ge "&dates_min"d) and (datepart(misc_rateeffectivedate) le "&dates_max"d)
and (cover_productclass = "&product");
run;

ERROR :

NOTE: There were 0 observations read from the data set AQUOTES.MULTIBRAND.
WHERE 0 /* an obviously FALSE WHERE clause */ ;
NOTE: The data set ID113.AQUOTEDATA has 0 observations and 124 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

Super User
Super User
Posts: 7,942

Re: Using date values in the Macros

Posted in reply to KiranMaddi

And have you tried my suggestion, puttin %trim() around the macro variables:

As in: ... ge "%trim(&DATES_MIN)"D...

 

You can see this in the log if you like, just put:

data _null_;

  put "--&date_min.--";

run;

 

into your editor.  The macro variable can have spaces in it, see if there is gaps after the first -- or before the --.  If there are spaces then your date value will not get created, hence it will try to find dates between . and . (i.e. missing.)

Frequent Contributor
Posts: 107

Re: Using date values in the Macros

Hi RW9,

Thanks for your reply. I have tried the trim function and that did not work either. There are no spaces wrapped.around.
Super User
Posts: 5,503

Re: Using date values in the Macros

Posted in reply to KiranMaddi

Also needed:  What is the value for &PRODUCT, and what is the domain of possible values for COVER_PRODUCTCLASS.  Remember, spelling/capitalization must be an exact match.

 

Frequent Contributor
Posts: 107

Re: Using date values in the Macros

Posted in reply to Astounding
&PRODUCT = "PC". Possible values are PC LC HH. Checked they all matched.
Ask a Question
Discussion stats
  • 21 replies
  • 549 views
  • 0 likes
  • 5 in conversation