BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dscamelo
Obsidian | Level 7

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.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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

 

 

 

 

View solution in original post

17 REPLIES 17
Reeza
Super User
How are semesters defined?
novinosrin
Tourmaline | Level 20
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
SAS Employee
@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!

Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF

View now: on-demand content for SAS users

novinosrin
Tourmaline | Level 20

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
Tourmaline | Level 20

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
Obsidian | Level 7

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
Tourmaline | Level 20

"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
Obsidian | Level 7

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

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

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
Obsidian | Level 7

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

 

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

Tom
Super User Tom
Super User

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
Quartz | Level 8

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 17 replies
  • 3582 views
  • 12 likes
  • 8 in conversation