Find year and months from quarterly date

[David_Billa]

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?

[Hnsqwzc0813]

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;

[David_Billa]

I believe month function returns only month value from the date. But I need
the month values of the quarter ending reporting date.

I need month values as 10,11,12 if date is 20171231

[Hnsqwzc0813]

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;

[Kurt_Bremser]

"10,11,12" cannot be stored as a number.

BTW, macro variables are always text.

[David_Billa]

I'm fine if months can be stored as text

[Kurt_Bremser]

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;

[Reeza]

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. 

[mkeintz]

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.));

[Ksharp]

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