topic Re: Custom Date Format in SAS Programming

Custom Date Format

dscamelo — Wed, 13 Feb 2019 18:37:05 GMT

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.

Re: Custom Date Format

Reeza — Wed, 13 Feb 2019 18:47:31 GMT

How are semesters defined?

Re: Custom Date Format

novinosrin — Wed, 13 Feb 2019 18:58:31 GMT

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

Re: Custom Date Format

noling — Wed, 13 Feb 2019 19:05:10 GMT

@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!

Re: Custom Date Format

novinosrin — Wed, 13 Feb 2019 19:12:13 GMT

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

Re: Custom Date Format

novinosrin — Wed, 13 Feb 2019 19:15:25 GMT

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?

Re: Custom Date Format

Andygray — Wed, 13 Feb 2019 19:44:07 GMT

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

Re: Custom Date Format

dscamelo — Wed, 13 Feb 2019 19:57:54 GMT

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.

Re: Custom Date Format

novinosrin — Wed, 13 Feb 2019 20:07:15 GMT

"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

Re: Custom Date Format

Reeza — Wed, 13 Feb 2019 20:27:48 GMT

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.

Re: Custom Date Format

Reeza — Wed, 13 Feb 2019 20:45:17 GMT

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;

Re: Custom Date Format

Tom — Wed, 13 Feb 2019 21:50:54 GMT

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

Re: Custom Date Format

ballardw — Wed, 13 Feb 2019 22:09:42 GMT

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.

Re: Custom Date Format

ChrisNZ — Wed, 13 Feb 2019 23:23:02 GMT

If you really want to define a format:

proc fcmp outlib=WORK.FUNCTIONS.CUSTOM;
  function yyhh(DATE) ;
    return (put(DATE,year4.)||put(ceil(qtr(DATE)/2.1),z2.));
  endsub;
run;

options cmplib=WORK.FUNCTIONS; 

proc format;
  value yyhh (default=6) '01jan1600'd - '01jan20000'd = [yyhh()] 
                         other                        = 'Invalid date';
run;

data _null_;
  YYHH = put('01sep2018'd, yyhh.) ; 
  put YYHH=; 
run;

YYHH=201802

Personally, I'd create the format as 2018H2 to avoid any confusion with months or quarters.

Re: Custom Date Format

ChrisNZ — Thu, 14 Feb 2019 00:40:13 GMT

Ha, I wanted to show off and support 5-digit years but forgot bits here and there...

This works even better:

proc fcmp outlib=WORK.FUNCTIONS.CUSTOM;
  function yyhh(DATE) ;
    return (put(DATE,year5.)||put(ceil(qtr(DATE)/2.1),z2.));
  endsub;
run;

options cmplib=WORK.FUNCTIONS; 

proc format;
  value yyhh (default=6) '01jan1600'd - '01jan20000'd = [yyhh()] 
                         other                        = 'NoDate';
run;

data _null_;
  YYHH = put('01sep12018'd, yyhh7.) ; 
  put YYHH=; 
  YYHH = put('01sep2018'd, yyhh.) ; 
  put YYHH=; 
run;

YYHH=1201802
YYHH=201802

Re: Custom Date Format

dscamelo — Thu, 14 Feb 2019 11:02:56 GMT

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

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

Re: Custom Date Format

dscamelo — Thu, 14 Feb 2019 11:18:05 GMT

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.

Re: Custom Date Format

dscamelo — Thu, 14 Feb 2019 11:48:11 GMT

That worked! Thank you, @ChrisNZ !

And thanks again to everyone who tried to help, especially @novinosrin!