BookmarkSubscribeRSS Feed
faisal_sas
Calcite | Level 5

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,

14 REPLIES 14
Haikuo
Onyx | Level 15

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
Calcite | Level 5

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
Lapis Lazuli | Level 10

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
Calcite | Level 5

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
Lapis Lazuli | Level 10

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
Calcite | Level 5

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
Lapis Lazuli | Level 10

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
Lapis Lazuli | Level 10

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
Onyx | Level 15

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
Onyx | Level 15

/*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
Calcite | Level 5

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
Onyx | Level 15

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
Super User

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 3897 views
  • 6 likes
  • 4 in conversation