Conditionally deleting observation from dataset

[faisal_sas]

Hi,

I have a dataset which updated monthly:

For exmaple: my dataset(sales) which get updated each month sales data for that particular month. First month i have data for jan next month will have data for jan & Feb, 3rd month i have data for jan,feb & march ...etc........ and next month i will have data for jan to april.

My question here is I want my dataset to have only current and previous month data, I do not want data from other previous months.

April - I should have data for march and april

may - I should have data for April and May

June - I should have data for May and June  etc.....

Could someone suggest me how to delete old months data(apart from current and previous month) using macro or automation...I do not want to do manually delete the old data each month using some where condition(where salesdate = 'date')

Thanks,

[Haikuo]

Some construct like this may work for you:

%macro clean(want=,have=);

data &want;

set &have;

if not missing(salesdate);

if month(today())-month(salesdate)<2;

run;

%mend;

%clean (want=,have=)

Regards,

Haikuo

[faisal_sas]

Thank You so much for your reply. Could you please help with me with a code that pulls last three months of data based on sales date (If I pulling the data on March 2012, I should get data for Dec 2011,Jan 2012 and Feb 2012. This should be be automated and run every month which takes data for the last three months.

[Linlin]

data have;

informat salesdate mmddyy10.;

format salesdate mmddyy10.;

input salesdate;

cards;

12/1/2011

11/12/2011

1/1/2012

9/12/2011

2/29/2012

3/1/2012

;

proc print;run;

data want;

set have;

if intnx('month',today(),-3,'b') <= salesdate < intnx('month',today(),0,'b');

proc print;run;

Obs     salesdate

1     12/01/2011

2     01/01/2012

3     02/29/2012

[faisal_sas]

Hi Linlin,

I am not doing this with data step. My backend data is in oracle and I am using SQL pass through facility to pull data...How would  I set a where condition that pulls data only for the last three months whenever I run this code. I want to automate this...I do not want to change the where condition whenever I run the code as per the month. Any help please....Thank You

If I give this where condition (

intnx('month',salesdate,3,'e') >today() and

    salesdate <intnx('month',today(),0,'b');)will it process in oracle database.

Thank You!

[Linlin]

Hi,

I use libname to pull data from oracle database. If there is no difference between SQL pass through  and Libname, then you can use proc sql:

proc sql;

  create table want as select * from have

  where

intnx('month',today(),-3,'b') <= salesdate < intnx('month',today(),0,'b');

quit;

This is how I assign libname:

libname yyyy oracle user="****"   password="&pd"   path="****" schema=****;

[faisal_sas]

Proc SQL pass through is very fast compare to libname access, the execution of codes not happen in SAS the codes will process in oracle.

I tried running your both code but not getting output, Please see ther log below

1    data have;

2    informat salesdate mmddyy10.;

3    format salesdate mmddyy10.;

4    input salesdate;

5    cards;

NOTE: The data set WORK.HAVE has 6 observations and 1 variables.

NOTE: DATA statement used (Total process time):

      real time           0.10 seconds

      cpu time            0.10 seconds

12   ;

13   proc print;run;

NOTE: There were 6 observations read from the data set WORK.HAVE.

NOTE: PROCEDURE PRINT used (Total process time):

      real time           0.04 seconds

      cpu time            0.04 seconds

14   proc sql;

15     create table want as select * from have

16     where

17   intnx('month',today(),-3,'b') <= salesdate < intnx('month',today(),0,'b');

NOTE: Table WORK.WANT created, with 0 rows and 1 columns.

18   quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.08 seconds

      cpu time            0.09 seconds

[Linlin]

from my log:

764  proc sql;

765

766    create table want as select * from have

767

768    where

769

770  intnx('month',today(),-3,'b') <= salesdate < intnx('month',today(),0,'b')

770! ;

NOTE: Table WORK.WANT created, with 3 rows and 1 columns.

771

772  quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

[Linlin]

data have;

informat salesdate mmddyy10.;

format salesdate mmddyy10.;

input salesdate;

cards;

12/1/2011

11/12/2011

1/1/2012

9/12/2011

2/29/2012

3/1/2012

;

proc sql;

  create table want as select * from have

  where

intnx('month',today(),-3,'b') <= salesdate < intnx('month',today(),0,'b');

quit;

proc print data=&syslast;

title from dataset &syslast;

run;

from dataset WORK.WANT                            08:31 Friday, March 16, 2012

Obs     salesdate

1     12/01/2011

2     01/01/2012

3     02/29/2012

[Haikuo]

What is your system time?

data _null_;

date=date();

put date date9.;

run;

If it is not March,2012, then you wouldn't get the same results as we do.

[Haikuo]

/*Current month and last month*/

%macro clean(want=,have=);

data &want;

set &have;

if intnx('month',today(),-1,'b') <= salesdate <= intnx('month',today(),0,'e');

run;

%mend;

/*last 3 month*/

%macro last3(want=,have=);

data &want;

set &have;

if intnx('month',today(),-3,'b') <= salesdate < intnx('month',today(),0,'b');

run;

%mend;

Please ditch the previous version that I had suggested, it will not work during the annual conjunctions.

Regards,

Haikuo

[faisal_sas]

I am not doing this with data step. My backend data is in oracle and I am using SQL pass through facility to pull data...How would  I set a where condition that pulls data only for the last three months whenever I run this code. I want to automate this...I do not want to change the where condition whenever I run the code as per the month. Any help please....Thank You

[Haikuo]

Well, SQL pass-through is native to Oracle, and it is definitely more efficient. But I know close to nothing to it except some SAS functions will not be compatible.

You could use data step by defining the library first:

libname yourlibrary oracle user= password= path=' schema=;

Haikuo

[Reeza]

How about creating a view that has a where clause in it that selects only the current and last month based on the current date?

Depending on your update procedure and the size of your dataset this is probably ideal in my head, as its always up to date and you don't need to remember to run it.

[faisal_sas]

Hi My source is oracle database. I want to pull data to SAS using SQL pass through facility. As per your idea I can create a view but my question is :

how would I set a where condition based on date that process in oracle database. Thank You