Custom Date Format

[dscamelo]

Hi, everyone.

I have a routine that separates data based on its semester. I need some help creating a custom date format that would output a date in the format YYYYSS (Y = Year, S = Semester), like this:

+-----------+---------+
|    DATE   |  OUTPUT |
+-----------+---------+
| 05FEB2018 |  201801 |
| 05AUG2018 |  201802 |
| 13JAN2019 |  201901 |
+-----------+---------+

Can anyone help me?

Thanks in advance.

[Reeza]

How are semesters defined?

[novinosrin]

data custom;
do d='01jan2018'd to '31dec2018'd;
m=month(d);
s=put(ceil(m/6),z2.);
length want $6;
want=cats(year(d),s);
output;
end;
format d date9.;
drop m s;
run;

So basically, 

put(ceil(m/6),z2.);

want=cats(year(d),s);

 should do

[noling]

@novinosrin is there a way to duplicate your logic in proc format instead of placing the desired format into your variable 'want'? From what I found the answer is no. Thoughts on using datastep logic and functions inside proc format? Thanks!

[novinosrin]

HI @noling   First off, Excellent observation!

I personally don't see a need for a custom format as such coz it's only gonna require another pass. The objective is rather straight forward data manipulation. of course, once that's done, one may turn that into a format using cntlin option and store that in a catalog. I don't see any reason to do all that for this case. 

Also, I can't think of a way to create the OP's custom format , if somebody can, I am all ears 

[novinosrin]

Hmm something is striking my mind now.

The logic that I applied could perhaps be converted into a user defined function first utilizing proc fcmp and then I vaguely remember reading somewhere that a function can be used in the values within proc format. But anyway, is it really worth the time and maintenance?  

[dscamelo]

Hi, @novinosrin, thanks for the quick reply.

I'm a bit confused, but that doesn't seem like it would work for any date, only for 2018. The idea would be to output my data to various tabels e.g. ORDERS_201701, PAYMENTS_201701, SALES_201702 , PAYMENTS_201702, ... , SALES_201901, PAYMENTS_201902.

My idea would be something like: the routine just got called, then the dates that I'm currently processing would be &have. in the following example. 

%LET dt_begin_sem	=	%SYSFUNC(INTNX(semiyear,&have.,0,b));
%LET dt_end_sem		=	%SYSFUNC(INTNX(semiyear,&have.,0,e));

Then, whenever a row had its date between the beginning and end of semester, it would be included in the table being currently generated. So table ORDERS_201701 would have everything from 01JAN2017 to 30JUN2017.

Another option would be a function that outputted semesters, but I haven't found anything like that.... Comparing the desired function "SMTR" to the existing function "QTR", it would be something like:

QTR(15JAN2017) = 1
QTR(20SEP2017) = 3
SMTR(15JAN2017) = 1
SMTR(20SEP2017) = 2

I hope I managed to make myself clear, english is not my first language.

Thanks again.

[novinosrin]

"I'm a bit confused, but that doesn't seem like it would work for any date, only for 2018."

That will work for any date. I used the full 2018 year as an example

using today as an example

data w;
d=today();
m=month(d);
s=put(ceil(m/6),z2.);
length want $6;
want=cats(year(d),s);
format d date9.;
run;

Anyways, there seems a bigger objective than just logic. 

Ok can you please outline your objective clearly with a sample data and also post a sample out for the input sample explaining what you want to accomplish?  Let's do away with bits and pieces. I am unable to exactly guage your need. Also, it would help if you could provide us samples of what you HAVE and what you WANT with some notes

[dscamelo]

Sure, @novinosrin,

My code is something like this...

/* This would be any date I need to separate data from */
%LET date_routine = %SYSFUNC(MDY(1,1,1992)); 

%LET semester_begin	= %SYSFUNC(INTNX(semiyear,&date_routine.,0,b));
%LET semester_end	= %SYSFUNC(INTNX(semiyear,&date_routine.,0,e));

/* This is the what I need, a way to quickly format the date by semester, without casting a sequence of strings*/
%LET formatted = %SYSFUNC(PUTN(&semester_begin.,yymmn6.));

PROC SQL;
	CREATE TABLE RETAIL_&formatted. AS
	SELECT
		*
	FROM
		sashelp.retail AS r
	WHERE
		r.date BETWEEN &semester_begin. AND &semester_end.;
QUIT;

Obviously, the "formatted" variable would be outputted to year and month, instead of year and semester. What I would need is something like the format YYMMn6, but instead a YY"SS"n6.

What I need should have been fairly simple, except that format doesn't exist. So I was trying to create it myself, but needed help.

Have I made it clearer?

Thanks again for trying to help.

[Reeza]

You still haven't defined what a 'semester' is. We need to know this information to code the formula, specifically which months belong to which semester. Your original example showed two, your latest shows three, the university I went to, had 4.

[Reeza]

Here's how a format could work:

data custom_fmt;
fmtname = 'myDateFmt';
type = 'N';
do start='01jan1990'd to '31dec2018'd;
m=month(start);
s=put(ceil(m/6),z2.);
length label $6;
label=cats(year(start),s);
output;
end;

run;

proc format cntlin=custom_fmt;
run;

proc print data=sashelp.stocks (obs=10);
where stock='IBM';
format date myDateFmt.;
run;

[ballardw]

A slightly different take on @Reeza's approach:

data custom_fmt_x;
fmtname = 'myDateFmt_x';
type = 'N';
length label $6;
do yr=1960 to 2999;
   start=mdy(1,1,yr);
   end  =mdy(6,30,yr);
   label=cats(yr,'01');
   output;
   start=mdy(7,1,yr);
   end  =mdy(12,31,yr);
   output;
   label=cats(yr,'02');
end;

run;

proc format cntlin=custom_fmt_x;
run;

This is splitting the year at June 30. But if you have a third semester such as a summer session we need to know the boundaries. If they change from year to year then even more fun.

Main difference in my approach is specify a start/end pair for ranges and use more years since each record covers half a year.

[dscamelo]

@Reeza, the one that has 3 was a quarter.

The word semester means "six months", and that is what I am going for.

[Tom]

SMTR(15JAN2017) = 1
SMTR(20SEP2017) = 2

Why not just test if month < 7?

smtr=1+month(date)>6;
want=cats(year(date),0,1+month(date)>6));

[Andygray]

@novinosrin   There's some secret to sharpness in the way your brain operates. 🙂 Classic!