topic Re: Find year and months from quarterly date in SAS Programming

Find year and months from quarterly date

David_Billa — Sat, 28 Nov 2020 06:21:01 GMT

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?

Re: Find year and months from quarterly date

Hnsqwzc0813 — Sat, 28 Nov 2020 06:59:54 GMT

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;

Re: Find year and months from quarterly date

Kurt_Bremser — Sat, 28 Nov 2020 07:05:01 GMT

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

BTW, macro variables are always text.

Re: Find year and months from quarterly date

David_Billa — Sat, 28 Nov 2020 08:37:57 GMT

I'm fine if months can be stored as text

Re: Find year and months from quarterly date

David_Billa — Sat, 28 Nov 2020 08:41:57 GMT

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

Re: Find year and months from quarterly date

Kurt_Bremser — Sat, 28 Nov 2020 09:02:13 GMT

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;

Re: Find year and months from quarterly date

Reeza — Sun, 29 Nov 2020 01:37:15 GMT

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.

Re: Find year and months from quarterly date

mkeintz — Sun, 29 Nov 2020 06:09:06 GMT

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

Re: Find year and months from quarterly date

Ksharp — Sun, 29 Nov 2020 11:23:16 GMT

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

Re: Find year and months from quarterly date

Hnsqwzc0813 — Mon, 30 Nov 2020 02:05:41 GMT

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;