Using a if and intnx date in another dataset

[new_sas_user_4]

Hi, 

I want the the current month date to refer to the prior months start date until 4th of the current month which I am able to do using the below code.

data A;

format CM date9.;

if day(today())>=5 then CM=intnx('month',today(),0,'b');

else CM=intnx('month',today(),-1,'b');

run;

Once I get CM I want to be able to use it in another program where I will use it in the where clause 

proc sql;

create table WANT as select * from A(a table in my library)

where month_dt= CM;

quit;

If i write it this way, I get the errror that CM does not exist in A 

Can this be done?

OR can CM be converted to a macro  so that it can be used in WANT ?

Thanks a lot in advance!!

[Reeza · Accepted solution]

1. Create a macro variable and use that in the query. There is no need to format the variable to appear as date9, that just makes it more complex.

data A;

format CM date9.;

if day(today())>=5 then CM=intnx('month',today(),0,'b');

else CM=intnx('month',today(),-1,'b');

 call symputx('cm', cm, 'g');

run;

%put &cm.;

2. Use a SQL query to select. Note that I've changed table names from A to make it less confusing. 

proc sql;
create table want as
select * from table1
where month_dt in (select cm from A);
quit;

---

@new_sas_user_4 wrote:

Hi, 

I want the the current month date to refer to the prior months start date until 4th of the current month which I am able to do using the below code.

data A;

format CM date9.;

if day(today())>=5 then CM=intnx('month',today(),0,'b');

else CM=intnx('month',today(),-1,'b');

run;

Once I get CM I want to be able to use it in another program where I will use it in the where clause 

proc sql;

create table WANT as select * from A(a table in my library)

where month_dt= CM;

quit;

If i write it this way, I get the errror that CM does not exist in A 

Can this be done?

OR can CM be converted to a macro  so that it can be used in WANT ?

Thanks a lot in advance!!

---

[new_sas_user_4]

Thanks a lot 🙂

[Jagadishkatam]

From the below program dataset A is generated but i dont see any SET statement to use the existing dataset. so i dont if the dataset A is actually created. please check, if it is then the proc sql should work if the month_dt is also available in dataset A along with date CM which you derived. 

data A;
set xxxx;?
format CM date9.;
if day(today())>=5 then CM=intnx('month',today(),0,'b');
else CM=intnx('month',today(),-1,'b');
run;

[Tom]

You are not reading any data into your dataset A. 

Are you just trying to make a dataset with only one observation based on the current date?

Do you need that dataset for anything?  If not then put the logic into your query.

proc sql;
create table WANT as
  select *
  from A
  where month_dt= 
case when (day(today())>=5) then intnx('month',today(),0,'b')
else intnx('month',today(),-1,'b') end
;
quit;

Or you can eliminate the CASE and just use boolean logic to generate the proper offset.

month_dt = intnx('month',today(),0-(day(today())<5),'b')

If your dataset is indexed on MONTH_DT then you might get better performance using macro code to make that date into a constant instead.

%let today=%sysfunc(today());
%let cm=%sysfunc(intnx(month,&today,%eval(0-(%sysfunc(day(&today)))<5),b));
....
where month_dt = &cm 
....

[new_sas_user_4]

Thanks everyone!!

I am sorry for the confusion with the table names..

The first table I create named A is just for creating the date and I want to use this date to filter from another table (lets call it X).