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
%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 ;
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;
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
@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.
@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.
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.
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.
And PS
That "Grandpa" reference was a meant to be a joke to lighten up the discussion.
%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 ;
Thanks,
Its works for me
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.