topic Re: Extract last three months columns based on financial year in SAS Programming

Extract last three months columns based on financial year

harshpatel — Wed, 02 Dec 2020 06:29:10 GMT

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

Re: Extract last three months columns based on financial year

andreas_lds — Wed, 02 Dec 2020 07:00:31 GMT

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;

Re: Extract last three months columns based on financial year

harshpatel — Wed, 02 Dec 2020 07:06:54 GMT

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

Re: Extract last three months columns based on financial year

andreas_lds — Wed, 02 Dec 2020 10:15:19 GMT

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

Re: Extract last three months columns based on financial year

PaigeMiller — Wed, 02 Dec 2020 11:55:37 GMT

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

Re: Extract last three months columns based on financial year

Ksharp — Wed, 02 Dec 2020 12:19:28 GMT

%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 ;

Re: Extract last three months columns based on financial year

Kurt_Bremser — Wed, 02 Dec 2020 13:06:46 GMT

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.

Re: Extract last three months columns based on financial year

harshpatel — Wed, 02 Dec 2020 13:17:00 GMT

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

Re: Extract last three months columns based on financial year

harshpatel — Wed, 02 Dec 2020 13:26:33 GMT

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

Re: Extract last three months columns based on financial year

Kurt_Bremser — Wed, 02 Dec 2020 13:27:53 GMT

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.

Re: Extract last three months columns based on financial year

harshpatel — Wed, 02 Dec 2020 13:46:26 GMT

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

Re: Extract last three months columns based on financial year

Kurt_Bremser — Wed, 02 Dec 2020 13:49:26 GMT

And PS

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

Re: Extract last three months columns based on financial year

harshpatel — Wed, 02 Dec 2020 14:06:29 GMT

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

Re: Extract last three months columns based on financial year

harshpatel — Mon, 07 Dec 2020 03:39:20 GMT

Thanks,

Its works for me