How to limit number of Months in SAS

Question

I have one column name "month" which has 31 months

what I want to do is I want to limit the number of months

for example:- Current Month followed by two future months and 3 previous months followed by current month

Like if the current month is April then it has to show something like

FEb-MAR-APR(Current month)-MAY- JUN

Anyone know, How we can do it in SAS and it should change dynamically?

Current table column Month (Format dtdate9.)

01JAN2016 01FEB2016 01MAR2016 01APR2016 01MAY2016 01JUN2016 01JUL2016 01AUG2016 01SEP2016 01OCT2016 01NOV2016 01DEC2016 01JAN2017 01FEB2017 01MAR2017 01APR2017 01MAY2017 01JUN2017 01JUL2017 01AUG2017 01SEP2017 01OCT2017 01NOV2017 01DEC2017 01JAN2018 01FEB2018 01MAR2018 01APR2018 01MAY2018 01JUN2018 01DEC2018

Output:- I need two columns max date and min date: Max date will give +2 and min date will be -3 and for every current month it should change dynamically. These two columns max and min i will use in my reporting tool SAS VA as month between min & max

Thanks in advance

Please add an example of your source table and how you want the final table to look like. — momo1644, May 01 '18 at 11:21
In addition to example of the data you HAVE and WANT, please add the code you have tried. Seeing your approach to the problem will help others help you. — Quentin, May 01 '18 at 11:24

momo1644 · Answer 1 · 2018-05-01T14:40:59.780

1

If you have tabular data then you only need to do two steps:

(optional step) Sort data by date not month name PROC SORT
Use INTCK() function to check the interval(in month) that you want and date() which returns today's date

Dummy Data:

data have;
length date 8. month $3. ;
input date month $ ;
informat date anydtdte20.;
format date ddmmyys10.;
datalines;
01/07/2018 JUL
01/02/2018 FEB
01/03/2018 MAR
01/04/2018 APR
01/05/2018 MAY
01/06/2018 JUN
01/08/2018 AUG
01/09/2018 SEP
;
run;

proc sort data=have out=sorted; by date; run;

Solution 1: Using Data Step

data want;
set sorted;
diff= intck('month',date(),date);
if (diff<= 2 and diff>= -3) then output;
run;

Solution 2: Using Proc SQL

proc sql;
create table want2 as
select * from sorted
where intck('month',date(),date) between 2 and -3 ;
quit;

Output:

date=01/02/2018 month=FEB diff=-3 
date=01/03/2018 month=MAR diff=-2 
date=01/04/2018 month=APR diff=-1 
date=01/05/2018 month=MAY diff=0 
date=01/06/2018 month=JUN diff=1 
date=01/07/2018 month=JUL diff=2

edited May 01 '18 at 14:40

answered May 01 '18 at 12:45

momo1644

1,769
9
25

HI thanks a lot for your reply, I want to do it in some different way like:- I will be calculating two fields max date +2 and min date.-3Then I will use this it for my report like Month between (min & max) – ASH May 01 '18 at 13:58
That's easy, please as an example of inputs and outputs (table view) to the question and I will update my answer. – momo1644 May 01 '18 at 14:11
Done, Please check – ASH May 01 '18 at 14:17
I've added another answer – momo1644 May 01 '18 at 14:37

score 1 · Accepted Answer · answered May 01 '18 at 14:37

1

Use INTNX() function to get a specific data range (increment or decrement)

Data:

data have;
input date ;
informat date date9.;
datalines;
01NOV2017 
01DEC2017 
01JAN2018 
01FEB2018 
01MAR2018 
01APR2018 
01MAY2018 
01JUN2018 
;
run;

Solution:

proc sql;
create table want as
select 
date format=date9. ,
intnx('month',date,-3) as min_date format=date9.,
intnx('month',date,+2) as max_date format=date9.
from have;
quit;

Output:

date=01NOV2017 min_date=01AUG2017 max_date=01JAN2018
date=01DEC2017 min_date=01SEP2017 max_date=01FEB2018
date=01JAN2018 min_date=01OCT2017 max_date=01MAR2018
date=01FEB2018 min_date=01NOV2017 max_date=01APR2018
date=01MAR2018 min_date=01DEC2017 max_date=01MAY2018
date=01APR2018 min_date=01JAN2018 max_date=01JUN2018
date=01MAY2018 min_date=01FEB2018 max_date=01JUL2018
date=01JUN2018 min_date=01MAR2018 max_date=01AUG2018

answered May 01 '18 at 14:37

momo1644

1,769
9
25

You can also do the similar thing in SAS VA using calculated fields in VA Report Designer. – momo1644 May 01 '18 at 14:40
ya, I did but my team lead wants to do it in data query. – ASH May 01 '18 at 15:10
I did it like this way But it is giving error'data clean1_data; set stag0(rename=(metric_month=month_dt)); metric_month=put(month_dt, date9.); drop month_dt; run; proc sql; create table want as select month_dt format=date9., intnx('month',metric_month,-3) as min_date format=date9., intnx('month',metricmonth,+2) as max_date format=date9. from clean1_data; quit;' – ASH May 01 '18 at 15:12
ERROR: Function INTNX requires a numeric expression as argument 2. ERROR: Character expression requires a character format. – ASH May 01 '18 at 15:13
What is the data type for metric_month? run: `proc sql; describe table stage0; quit;` – momo1644 May 01 '18 at 15:21
metric_month Char 15 $15. $15. metric_month – ASH May 01 '18 at 15:24
you have to convert it to date or user another column, try `input(metric_month,anydtdte20.)` – momo1644 May 01 '18 at 15:28
intnx('month',input(metric_month,anydtdte20.),-3) as min_date format=date9., intnx('month',input(metric_month,anydtdte20.),+2) as max_date format=date9. – momo1644 May 01 '18 at 15:29
You are welcome, if have sas VA question I will also be able to help. – momo1644 May 02 '18 at 06:35
Yup for sure, But when I am adding it as a filter in crosstabs like Month incl between max and min. Its not showing the data item max and min – ASH May 02 '18 at 10:40
Check the default aggregation which VA is applying to those fields. you need to filter on 1 month at a time in the cross table then use min or max aggregation and SAS VA is probably using a default avg or sum. Please create another Question for SAS VA if it is still not working. – momo1644 May 02 '18 at 10:49
Hi @momo1644 i have a question for you please check https://stackoverflow.com/questions/50334174/how-do-i-create-a-cumulative-median-field-in-sas-va-7-4-designer – ASH May 14 '18 at 15:40

How to limit number of Months in SAS

2 Answers2