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.
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
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
Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF
View now: on-demand content for SAS users
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
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?
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.
"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
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.
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;
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.
@Reeza, the one that has 3 was a quarter.
The word semester means "six months", and that is what I am going for.
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));
@novinosrin There's some secret to sharpness in the way your brain operates. 🙂 Classic!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.