topic Re: Conditionally deleting observation from dataset in SAS Programming

Conditionally deleting observation from dataset

faisal_sas — Fri, 16 Mar 2012 13:11:00 GMT

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,

Re: Conditionally deleting observation from dataset

Haikuo — Fri, 16 Mar 2012 13:27:32 GMT

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

Re: Conditionally deleting observation from dataset

Reeza — Fri, 16 Mar 2012 14:20:57 GMT

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.

Re: Conditionally deleting observation from dataset

faisal_sas — Fri, 16 Mar 2012 17:31:24 GMT

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.

Re: Conditionally deleting observation from dataset

Haikuo — Fri, 16 Mar 2012 17:46:37 GMT

/*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

Re: Conditionally deleting observation from dataset

Linlin — Fri, 16 Mar 2012 17:46:46 GMT

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

Re: Conditionally deleting observation from dataset

faisal_sas — Fri, 16 Mar 2012 18:02:53 GMT

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

Re: Conditionally deleting observation from dataset

faisal_sas — Fri, 16 Mar 2012 18:06:03 GMT

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

Re: Conditionally deleting observation from dataset

Haikuo — Fri, 16 Mar 2012 18:13:52 GMT

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

Re: Conditionally deleting observation from dataset

faisal_sas — Fri, 16 Mar 2012 18:22:10 GMT

Hi Linlin,

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!

Re: Conditionally deleting observation from dataset

Linlin — Fri, 16 Mar 2012 18:35:17 GMT

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=****;

Conditionally deleting observation from dataset

faisal_sas — Fri, 16 Mar 2012 18:40:56 GMT

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

Conditionally deleting observation from dataset

Linlin — Fri, 16 Mar 2012 18:47:29 GMT

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

Conditionally deleting observation from dataset

Linlin — Fri, 16 Mar 2012 18:52:53 GMT

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

Re: Conditionally deleting observation from dataset

Haikuo — Fri, 16 Mar 2012 18:56:46 GMT

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.