Extract last three months columns based on financial year

[harshpatel]

Hi,

I need to extract last three months columns based on financial year.

My dataset contains columns like

201910   201911   201912   202001   202004   202005  202006   

Now suppose if someone will pass a parameter as below

%let monyear=202004

then column should get filtered as 202001   202003  and 202004

But suppose someone will pass a parameter as below

%let monyear=202001

then my column should get filtered as 201910   201912 and 202001

Kindly help me to solve this issue

Note: First four digit contains year and last two digits contains month.

Thanks,

Harsh

[andreas_lds]

Having data in the name of variable is almost always a bad idea and leads to unnecessary complex code. To solve your problem start by transposing the data, so that you have a variable with the year/month information. In a subsequent step use that variable to create a sas-date and change the value of your macro-variable to

%let monyear = '01Apr2020'd;

so that it contains a sas-date, too. Last step: Use a where statement to select the information of interest, like

data april;
  set transposed_fixed_have;
  where intnx('month', &monyear. ,  -3) <= dateVar <= &monyear.;
run;

[harshpatel]

Thanks,

But my column format is 202004

and i want to create 3 macro variables

IF %let year=202004 then %let year1=202001 (year-3)  and %let year2=202003 (year-1)

But if %let year=202001 then %let year1=201910  and %let year2=201912

[andreas_lds]

---

@harshpatel wrote:

Thanks,

But my column format is 202004

and i want to create 3 macro variables

IF %let year=202004 then %let year1=202001 (year-3)  and %let year2=202003 (year-1)

But if %let year=202001 then %let year1=201910  and %let year2=201912

---

2020204 is not a valid name for a sas variable, even if the option validvarname=any is active, it is strongly recommended to use "old-school" variable names. Using "partial" dates makes things worse, and as i already said, unnecessary complicated. With a proper date (as value) you can use the function intnx to shift the value without having to take care to maintain a valid date manually.

[PaigeMiller]

---

@harshpatel wrote:

But my column format is 202004

---

It simply doesn't matter that your column format is 202004. Transpose the data, and your coding becomes simple. SAS has already done the behind the scenes work to make this simple, if only you will go that way.

Leave the data as it is with calendar dates as variable names, and you need macro variables and other complications and your coding becomes difficult.

[Kurt_Bremser]

Please believe us when we give you advice, born from decades of experience in the field. A dataset structure like yours is USELESS. TRANSPOSE before you do anything else. Really. I mean it. Ask your Grandpa if you don't believe me.

[harshpatel]

Hi,
I have not written any contradictory statement, I have just asked for help and when I got solution I have just thanks to those who has given me solution,
So don't say any contradictory statement
Be professional

[Kurt_Bremser]

I AM being professional, and I AM trying to help you out of 20+ years of SAS experience and close to 4 decades as a programmer.

So suppose someone gave you a spreadsheet (most of such useless data structure comes from there) like this:

data bad;
input ID $ '201910'n '201911'n '201912'n '202001'n '202002'n '202003'n '202004'n '202005'n '202006'n;
datalines;
A 1 2 3 4 5 6 7 8 9
;

The first thing you do is make this unintelligent and unhelpful data into intelligent data (see Maxim 33):

First, transpose, so that data goes from structure to content:

proc transpose
  data=bad
  out=long
;
by ID;
var '201910'n--'202006'n;
run;

Next, make date values out of the strings:

data good;
set long;
period = input(_name_,yymmn6.);
format period yymmd7.;
rename col1=value;
drop _name_;
run;

Now, your initial issue turns into extremely simple code:

%let monyear=202004;
/* make this value into a SAS date value: */
%let period=%sysfunc(inputn(&monyear,yymmn6.));

data want;
set good;
where intnx('month',&period.,-2,'b') le period le &period.;
run;

You can find a gazillion of posts here on the communities where the merits of a long dataset structure are shown and proven. Please heed our advice.

[harshpatel]

Thanks for your help,
I will apply this logic.
It will definitely work

[harshpatel]

I have already accepted the solution
And next time don't say that ask your grandpa,
I know you are a born decade expert and a experienced guy but don't be rude and arrogant.
If you can't do help then simply tell that this is not possible

[Kurt_Bremser]

And PS

That "Grandpa" reference was a meant to be a joke to lighten up the discussion.

[harshpatel]

Yup it's ok,
I can understand
Thanks for your help

[Ksharp · Accepted solution]

%let monyear=202001 ;

%let temp=%sysfunc(inputn( &monyear. , yymmn6.)) ;
%let m1=%sysfunc(intnx(month, &temp , -1 ),yymmn6.);
%let m2=%sysfunc(intnx(month, &temp , -2 ),yymmn6.);


%put &m2 &m1 &monyear ;

[harshpatel]

Thanks,

Its works for me