I've a macro variable and it will always have a quarter ending reporting date. Now I want to find the year and months value from that macro variable.
Example:
%let Reporting_date=20171231;
Excepted Results: I want to create a variables as below with the excepted values and it should be numeric.
Year=2017
Months=10,11,12
Any help?
You can try to use this way :
data test ;
year=year(input("&Reporting_date",yymmdd8.));
months=month(input("&Reporting_date",yymmdd8.));
put year=/months=;
run;
You can use qtr function and ' if then ' again
%let Reporting_date=20171231;
data test ;
length months $8;
year=year(input("&Reporting_date",yymmdd8.));
if qtr(input("&Reporting_date",yymmdd8.))=1 then months='1,2,3';
else if qtr(input("&Reporting_date",yymmdd8.))=2 then months='4,5,6';
else if qtr(input("&Reporting_date",yymmdd8.))=3 then months='7,8,9';
else if qtr(input("&Reporting_date",yymmdd8.))=4 then months='10,11,12';
put year=/months=;
run;
"10,11,12" cannot be stored as a number.
BTW, macro variables are always text.
Use a data _null_ step:
data _null_;
call symputx('year',substr("&reporting_date",1,4));
select (substr("&reporting_date",5,2));
when ("01","02","03") months = "01,02,03";
when ("04","05","06") months = "04,05,06";
when ("07","08","09") months = "07,08,09";
when ("10","11","12") months = "10,11,12";
end;
call symputx('months',months);
run;
Essentially, this is a look up of sorts so can use INTNX() + loop, SELECT/IF/CASE statements, formats, lookup table merge, a custom function, or temporary array. The optimal solution usually depends on what you're doing next.
Once you've generated a sas date from the reporting_date macrovar, the catx function will easily generate the csv list of months:
%let Reporting_date=20171231;
data _null_;
d0=intnx('qtr',input("&reporting_date",yymmdd8.),0,'BEG');
m0=month(d0);
year=year(d0);
length months $8;
months=catx(',',m0,m0+1,m0+2);
put year= months=;
run;
Now for dates in Jan through Sep, the month list won't have leading zeroes (i.e.20170331 generates 1,2,3 not 01,02,03). If you want leading zeroes, then use
months=catx(',',put(m0,z2.),put(m0+1,z2.),put(m0+2,z2.));
%let Reporting_date=20171231;
data have;
year=%substr(&Reporting_date,1,4);
temp=%substr(&Reporting_date,5,2);
month=catx(',',temp-2,temp-1,temp);
run;
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.