BookmarkSubscribeRSS Feed
David_Billa
Rhodochrosite | Level 12

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?

9 REPLIES 9
Hnsqwzc0813
Calcite | Level 5

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
Rhodochrosite | Level 12
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
Calcite | Level 5

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;

David_Billa
Rhodochrosite | Level 12
I'm fine if months can be stored as text
Kurt_Bremser
Super User

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
Super User

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
PROC Star

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.));
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Ksharp
Super User
%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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 1842 views
  • 1 like
  • 6 in conversation