topic Re: Number series for past 12 months in SAS Procedures

Number series for past 12 months

new_sas_user_4 — Thu, 18 Jul 2019 19:25:34 GMT

Hi Everyone,

I want number series for the past one year for subsetting data:

Eg: If I am in July, I want to get the past 12 months data.

Proc sql;

create table A as select * from B where (month in (1,2,3,4,5,6) and year in (2019)) or (month in (7,8,9,10,11,12) and year in (2018));

run;

The month and year fields are numeric in table "B".

How can this be done?

Thank you 🙂

Re: Number series for past 12 months

ballardw — Thu, 18 Jul 2019 19:36:22 GMT

@new_sas_user_4 wrote:

Hi Everyone,

I want number series for the past one year for subsetting data:

Eg: If I am in July, I want to get the past 12 months data.

Proc sql;

create table A as select * from B where (month in (1,2,3,4,5,6) and year in (2019)) or (month in (7,8,9,10,11,12) and year in (2018));

run;

The month and year fields are numeric in table "B".

How can this be done?

Thank you 🙂

Did that code not work? As long as the from table name is a valid SAS data set and the variable names in the data a described that should work.

Re: Number series for past 12 months

Reeza — Thu, 18 Jul 2019 19:38:28 GMT

What is the type and format on your date variables?

This can be done, but how depends on your data structure.

Re: Number series for past 12 months

new_sas_user_4 — Thu, 18 Jul 2019 19:43:09 GMT

This works fine, But I want to automate it so the next month ...say August 2019 I wont have to re-write :

Proc sql;

create table A as select * from B where (month in (1,2,3,4,5,6,7) and year in (2019)) or (month in (8,9,10,11,12) and year in (2018));

run;

The values for month and year should be filled in automatically in the code based on the month I am in!

Re: Number series for past 12 months

Reeza — Thu, 18 Jul 2019 19:54:22 GMT

What is the type and format on your date variables?

It's much more efficient to use INTNX or INTCK instead of manually doing these calculations each month, but to do that you need to have a SAS date. Then it becomes.

where date between intnx('month', today(), 0, 'b') and intnx('month', today(), -12, 'b');

You'll probably need to tweak the elements a bit but in general that's a more robust method.

@new_sas_user_4 wrote:

This works fine, But I want to automate it so the next month ...say August 2019 I wont have to re-write :

Proc sql;

create table A as select * from B where (month in (1,2,3,4,5,6,7) and year in (2019)) or (month in (8,9,10,11,12) and year in (2018));

run;

The values for month and year should be filled in automatically in the code based on the month I am in!

Re: Number series for past 12 months

ballardw — Thu, 18 Jul 2019 20:19:12 GMT

@Reeza wrote:

What is the type and format on your date variables?

It's much more efficient to use INTNX or INTCK instead of manually doing these calculations each month, but to do that you need to have a SAS date. Then it becomes.
where date between intnx('month', today(), 0, 'b') and intnx('month', today(), -12, 'b');
You'll probably need to tweak the elements a bit but in general that's a more robust method.

@new_sas_user_4 wrote:

This works fine, But I want to automate it so the next month ...say August 2019 I wont have to re-write :

Proc sql;

create table A as select * from B where (month in (1,2,3,4,5,6,7) and year in (2019)) or (month in (8,9,10,11,12) and year in (2018));

run;

The values for month and year should be filled in automatically in the code based on the month I am in!

Might want

where date between intnx('month', today(), 0, 'e') and intnx('month', today(), -12, 'b');

if the end of the current month is the goal.

Date values are generally preferred instead of separate month and year fields as a single value manipulates easily for many tasks.

If you don't have a day of the month then pick a day to assume for such purposes and create it with

date= mdy(month,1,year) for first day of the month. Other options are available if desired but which day needed would need to be known to modify code.

Re: Number series for past 12 months

Reeza — Thu, 18 Jul 2019 20:27:52 GMT

where date between intnx('month', today(), -13, 'b') and intnx('month', today(), -1, 'e');

Since it's from July 2018 to June 2019 it's likely the above.

Re: Number series for past 12 months

novinosrin — Thu, 18 Jul 2019 23:49:36 GMT

Hello @new_sas_user_4 Follow this approach if you like,

/*Since you didn't provide a sample I create a sample have with year and month variables assuming you do not have a date variable*/
data have;
do year=2015 to 2020;
do month=1 to 12;
output;
end;
end;
run;

/*Create a look up table, this logic will dynamically create the last 12 months from he current month*/
data look_up;
k=12-month(today());
do month=month(today())-1 to 1 by -1;
year=year(today());
output;
end;
do month=12 to 12-k by -1;
year=year(today())-1;
output;
end;
keep month year;
run;


/*Straight forward Look up from your have on to the look up table and output the matches*/
data want ;
if _n_=1 then do;
   dcl hash H (dataset:'look_up') ;
   h.definekey  ("month","year") ;
   h.definedone () ;
end;
set have;
if h.check()=0;
run;

Re: Number series for past 12 months

novinosrin — Thu, 18 Jul 2019 21:39:59 GMT

And if you truly understand the above,

The lookup table step and the hash step can be combined into one. However , no biggie as you are only creating 12 records in the look up table, so may as well keep it as suggested previously

data want ;
if _n_=1 then do;
   dcl hash H () ;
   h.definekey  ("month","year") ;
   h.definedata ("month","year") ;
   h.definedone () ;
   k=12-month(today());
   do month=month(today())-1 to 1 by -1;
   year=year(today());
   h.add();
   end;
   do month=12 to 12-k by -1;
   year=year(today())-1;
   h.add();
   end;
end;
set have;
if h.check()=0;
drop k;
run;

Re: Number series for past 12 months

new_sas_user_4 — Thu, 18 Jul 2019 22:08:54 GMT

I think the solutions with intnx will work for my query...

I didnt understand this part @novinosrin :

data want ;
if _n_=1 then do;
   dcl hash H (dataset:'look_up') ;
   h.definekey  ("month","year") ;
   h.definedone () ;

Re: Number series for past 12 months

novinosrin — Thu, 18 Jul 2019 23:47:13 GMT

@new_sas_user_4 Yes if you do have a Date variable, you are better of using intnx. That's absolute!

The solution I gave you assumes you do not have a date variable. Also, as @Reeza pointed out, you could help us to help you by providing a sample of what you have. That would not lead to assumptions.

Re: Number series for past 12 months

novinosrin — Fri, 19 Jul 2019 11:45:24 GMT

The SQL equivalent of the above HASH would be


proc sql;
create table want as
select a.*
from have a inner join look_up b
on a. month=b.month and a.year=b.year;
quit;

Again all of my suggestions assumes you do not have a date variable