topic Re: Comparing Macro Variable date to date literal in SAS Programming

Comparing Macro Variable date to date literal

mhodge20 — Tue, 06 Feb 2018 18:24:44 GMT

%let x = 01/01/2018;

%macro loop;

%if &x. ge '01Jan2017'd %then leave;

%mend;

How do I accurately compare the date that is the macro variable and the date literal?

Re: Comparing Macro Variable date to date literal

Reeza — Tue, 06 Feb 2018 18:25:46 GMT

1. Either define the macro variable as a data literal like you did in the code

2. Use INPUT to convert the macro variable to a date literal to allow the comparison.

@mhodge20 wrote:

%let x = 01/01/2018;

%macro loop;

%if &x. ge '01Jan2017'd %then leave;

%mend;

How do I accurately compare the date that is the macro variable and the date literal?

Re: Comparing Macro Variable date to date literal

mhodge20 — Tue, 06 Feb 2018 18:29:00 GMT

I would need a %sysfunc and inputn?

Re: Comparing Macro Variable date to date literal

PaigeMiller — Tue, 06 Feb 2018 18:39:03 GMT

@mhodge20 wrote:

%let x = 01/01/2018;

%macro loop;

%if &x. ge '01Jan2017'd %then leave;

%mend;

How do I accurately compare the date that is the macro variable and the date literal?

%let x = '01JAN2018'd;
%macro loop;
%if &x. ge '01Jan2017'd %then leave;
%mend;

Re: Comparing Macro Variable date to date literal

Reeza — Tue, 06 Feb 2018 18:41:33 GMT

If you're going down the conversion route, then yes.

@mhodge20 wrote:
I would need a %sysfunc and inputn?

Re: Comparing Macro Variable date to date literal

novinosrin — Tue, 06 Feb 2018 18:46:25 GMT

use sysevalf:

%let x = '01JAN2018'd;
%macro loop;
%if %sysevalf(&x. ge '01Jan2017'd) %then %put leave;
%mend;

%loop

Re: Comparing Macro Variable date to date literal

Astounding — Tue, 06 Feb 2018 18:46:27 GMT

You do have more work ahead of you.

In macro language, 01/01/2018 means 1, divided by 1, divided by 2018. That's why you need the conversion. (Yes, you are mentioning the correct tools.)

However, macro language will not interpret date literals as is. You will need to wrap the date literal with:

%sysevalf('01Jan2017'd)

Finally, I don't think LEAVE is a macro language statement. I could be wrong here. But I believe that %LOOP is saying to make the comparison, and if the comparison is true add the word LEAVE to your SAS program. To correct that, you would have to show more of what is inside the definition of %LOOP.

Re: Comparing Macro Variable date to date literal

mhodge20 — Tue, 06 Feb 2018 18:46:59 GMT

the %let is actually a loop im spitting out not me writing a date literal.

%do II = 1 %to 12
%let x = %sysfunc(intnx(month,&termdate.,&ii,B),Date9.);

%if &x. ge '01FEB2018'd %then leave;
%else;

Re: Comparing Macro Variable date to date literal

mhodge20 — Tue, 06 Feb 2018 18:49:36 GMT

If the comparison is true then leave the loop.

Re: Comparing Macro Variable date to date literal

Reeza — Tue, 06 Feb 2018 18:51:08 GMT

You probably need a %LEAVE

And if you're using INTNX it will automatically return a SAS date so you don't need to worry about the format/appearance.

Re: Comparing Macro Variable date to date literal

mhodge20 — Tue, 06 Feb 2018 18:52:44 GMT

I thought the macrovariable would be saved as a character string and I would have to convert the string to date in order to compare it with a date literal?

Re: Comparing Macro Variable date to date literal

novinosrin — Tue, 06 Feb 2018 18:58:24 GMT

@Reeza There is no %leave like leave in datastep. I wonder why the OP is so much into macro in the first place. In most cases, perhaps there is no need for maco.

Anyway to exit the loop, if the OP is using a iterative %do loop. Once the %if condition is satisfied, OP has to reset the the do index variable to stop value or use %do %until(conditon=true) . My 2 cents

Re: Comparing Macro Variable date to date literal

Reeza — Tue, 06 Feb 2018 19:01:43 GMT

@novinosrin You're correct.

You can also use a standard %IF/%THEN/%ELSE to control the loop, but we'd need more of the code or the requirements to understand what the best approach is. For the OP, there's an example of looping with dates in the SAS macro appendix.

http://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n01vuhy8h909xgn16p0x6rddpoj9.htm&docsetVersion=9.4&locale=en

Re: Comparing Macro Variable date to date literal

mhodge20 — Tue, 06 Feb 2018 19:06:27 GMT

I had the loop spit out a Date9. and then wrapped it in %str(%') to produce a date literal.

%let x = %sysfunc(intnx(month,&termdate.,&ii,B),Date9.);
%let t = %str(%')&x.%str(%')d;

%put &t.

'01FEB2015'd

%if &t. ge '01FEB2018'd %then leave;

Re: Comparing Macro Variable date to date literal

Astounding — Tue, 06 Feb 2018 19:16:03 GMT

Spoiler: You are still a long way from having useful code.

The word LEAVE is still not going to be useful.

The comparison is still incorrect even if there are no syntax errors. (Try using '01SEP2015'd vs. '01FEB2018'd for example.)

Rather than fix those issues, you would be better advised to show how %LOOP is supposed to be used.

Re: Comparing Macro Variable date to date literal

mhodge20 — Tue, 06 Feb 2018 19:26:06 GMT

I didnt mean to put the spolier lines there. grey bars showed up and I didnt know what they were or how to remove them.

But this is what im trying to Accomplish.

I have a list of datasets that are sequentially names with year and month. Ex. Perf_200504 - Perf_201801

I have a list of dates in another table by client.

I am trying to stack client relevant data.

So if client 1234 date is 01/01/2016, stack all client information from each dataset.

So I need to loop through each dataset from the point of a specific date and stack all later datasets.

Hope this makes sense

Re: Comparing Macro Variable date to date literal

Astounding — Tue, 06 Feb 2018 19:40:24 GMT

OK, some key information then:

What is the LIBNAME for the folder holding the data?
What is the LIBNAME for the separate table holding the list of clients?
In that list of clients, how is that date field stored? As a character string? As a SAS date? What is the actual value, and what is the formatted value (if that's different than the actual value)?

Can I assume from your example (where the date is 01/01/2016) that you want to extract everything from Perf_201601, everything from Perf_201602, all the way through everything from Perf_201801?

Re: Comparing Macro Variable date to date literal

mhodge20 — Tue, 06 Feb 2018 19:43:24 GMT

libname for perf data is perf
list of clients and dates are in work, in dataset called clients

The date is a sas date formatted as MMDDYY10.

and the assumption at the bottom is correct

Re: Comparing Macro Variable date to date literal

Astounding — Tue, 06 Feb 2018 21:26:50 GMT

One way to go:

%macro one_client (client=);

%local starting_ym dataset_list;

data start;

set clients;

where client = &client;

call symputx('starting_ym', catt(year(date), month(date));

run;

proc sql;

select distinct 'perf.' || memname into : dataset_list separated by ' '

from dictionary.tables

where substr(upcase(memname), 1, 5)) = 'PERF_' and substr(memname, 6, 6) >= "&starting_ym"

;

quit;

data want;

set &dataset_list;

run;

%mend one_client;

Obviously, it's untested code. Minor tweaks might be needed.