topic Re: Filter date value based on Macro in SAS Programming

Filter date value based on Macro

Babloo — Wed, 19 Dec 2018 13:53:38 GMT

I've a macro as follows.

%let reporting_dt=30JUN2018

Then I've to filter the below variable and it should be between 01jun2018 to 30jun2018 of the 'reporting_dt'. I don't want to hard code anything here as the value of the macro variable changes frequently.

Trans_date

01jun2018
08jul2017
30apr2018
27jun2018

Desired output:

Trans_date

01jun2018
27jun2018

Re: Filter date value based on Macro

RW9 — Wed, 19 Dec 2018 14:02:14 GMT

AS with every other post, please show test data in the form of a datastep. Now, once again we have to have this back and forth as we try to extract the required information from you as to what the data is!!

What is trans_date? Is it a character or a numeric? How are you filtering the data?

If its a date then:

data want;
  set have;
  where trans_date="&reporting_dt."d;
run;

If its character:

data want;
  set have;
  where upcase(trans_dt)="&reporting_dt.";
run;

In future, please post test data in the form of a datastep, show what output you want, show the code you have used, and any log output which occurs.

Re: Filter date value based on Macro

PaigeMiller — Wed, 19 Dec 2018 14:27:20 GMT

@Babloo wrote:

I've a macro as follows.
%let reporting_dt=30JUN2018

It will help everyone if we all are using the same (and correct) terminology. You do NOT have a macro. You have a macro variable.

Then I've to filter the below variable and it should be between 01jun2018 to 30jun2018

So how does the macro variable relate to the above sentence? There is more information in the above sentence than there is in the macro variable. Where does this extra information come from? We need to understand the logic in order to provide working code.

Re: Filter date value based on Macro

Astounding — Wed, 19 Dec 2018 14:19:46 GMT

Well, you haven't told us a lot ... is TRANS_DATE numeric? What should happen if &REPORTING_DT falls in the middle of the month. Do you need to use SQL, or will a DATA step suffice?

So making a guess as to the answers, here is an approach:

data want;

set have;

where intnx('month', "&reporting_dt"d, 0) <= trans_date <= "&reporting_dt"d;

run;

Re: Filter date value based on Macro

Babloo — Wed, 19 Dec 2018 15:05:47 GMT

TRANS_DATE is numeric variable. I need all the dates of the reporting month
in TRANS_DATE. Data step or proc sql, anything is OK

Re: Filter date value based on Macro

ballardw — Wed, 19 Dec 2018 15:34:11 GMT

@Babloo wrote:
TRANS_DATE is numeric variable. I need all the dates of the reporting month
in TRANS_DATE. Data step or proc sql, anything is OK

It sounds like you are looking for something like:

data want;
   set have;
   where intnx('month',"&reporting_dt."d,0,'B') le trans_date le  intnx('month',"&reporting_dt."d,0,'E');
run;

Which assumes that your trans_date is SAS date valued.