Number series for past 12 months

[new_sas_user_4]

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 🙂

[ballardw]

---

@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.

[Reeza]

What is the type and format on your date variables?

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

[new_sas_user_4]

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!

[Reeza]

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!

---

[ballardw]

---

@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. 

[Reeza]

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.

[novinosrin]

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;

[novinosrin]

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;

[new_sas_user_4]

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 () ;

[novinosrin]

@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.

[novinosrin]

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