Extracting Month, Year and quarter

[mlogan]

Hi All,

I have the following table with date and time.

Name	Invoice_Date
A	2014-02-12 20:12
B	2014-04-01 19:00
D	2014-05-03 23:31
E	2014-07-29 17:10
R	2014-09-15 10:31
F	2015-03-10 12:12
G	2015-06-02 21:18
GF	2015-06-09 10:13
DF	2015-06-27 15:11

data Out;

informat Invoice_Date datetime16.;

date = datepart(dob);

month = month(date);

year = year(date);

quarter = qtr(date);

Set have;

run;

Can someone tell me how do I extract Year Month YearMonth and Quarter like the following table please.

Name	Invoice_Date	Year	Month	Yearmonth	Quarter
A	2014-02-12 20:12	2014	Feb	Feb-2014	1
B	2014-04-01 19:00
D	2014-05-03 23:31
E	2014-07-29 17:10
R	2014-09-15 10:31
F	2015-03-10 12:12
G	2015-06-02 21:18
GF	2015-06-09 10:13
DF	2015-06-27 15:11

Thanks,

[Fugue]

data have;

input name $ 1-2

      invoice_datechar $ 5-20 ;

format invoice_date datetime.;

invoice_date = input ( invoice_datechar, anydtdtm.);

datalines;

A     2014-02-12 20:12

B     2014-04-01 19:00

D     2014-05-03 23:31

E     2014-07-29 17:10

R     2014-09-15 10:31

F     2015-03-10 12:12

G     2015-06-02 21:18

GF    2015-06-09 10:13

DF    2015-06-27 15:11

;;;;;

run;

data want;

      set have;

      format invoice_yearmonth YYMON7.

            invoice_quarter qtr1.;

      invoice_year=year(datepart(invoice_date));

      invoice_month=put(datepart(invoice_date), monname3.);

      invoice_yearmonthchar=compress(put(datepart(invoice_date), monname3.)||'-'|| invoice_year);

      invoice_yearmonth=datepart(invoice_date);

      invoice_quarter=datepart(invoice_date);

;

run;

[RW9]

Just a note Fugue, your code is less optimized than the OP.  You call datepart() five times.  I would suggest your better off creating a variable to hold datepart (not just to save procedure call, but maybe for further processing) and then dropping that variable if not needed.

[gcleggs]

Hello mlogan

  Examine and modify the following code. I did not use the time portion as part of the date string since the example you presented did not use it either. Run code and examine output, Use as you see fit.

data have;

input @1 name $char2.

      @4 invoice_str $CHAR10.

      @15 inv_time $CHAR5.;

/*format invoice_date datetime.;*/

invoice_date = input ( invoice_str, yymmdd10.);

datalines;

A,2014-02-12,20:12

B,2014-04-01,19:00

D,2014-05-03,23:31

E,2014-07-29,17:10

R,2014-09-15,10:31

F,2015-03-10,12:12

G,2015-06-02,21:18

GF,2015-06-09,10:13

DF,2015-06-27,15:11

;

run;

data want;

      set have;

       invoice_year=year(invoice_date);

      invoice_month=PUT(invoice_date, monname3.);

      invoice_yearmonth=invoice_month || '-' || invoice_year;

      invoice_quarter=PUT(QTR(invoice_date),3.);

run;

proc print data=want;

run;

[mlogan]

HI gcleggs,

Thanks for the code, your code was working but can you modify the code so that all observations shows up, right now first 7 observations are mossing.

Thanks,

[RW9]

Hi,

Your code is fine as it is.  All you need to do is move the set command up to the top, i.e. processing is working, however until the set command is encountered no data is present.

data Out;

  Set have;

  date = datepart(invoice_date);

  month = month(date);

  year = year(date);

  quarter = qtr(date);

run;

[Steelers_In_DC · Accepted solution]

Here you go:

data have;

infile cards dsd;

informat Name $2.    Invoice_Date ymddttm16.;

format Name $2.    Invoice_Date datetime.;

input Name    Invoice_Date;

cards;

A,2014-02-12 20:12

B,2014-04-01 19:00

D,2014-05-03 23:31

E,2014-07-29 17:10

R,2014-09-15 10:31

F,2015-03-10 12:12

G,2015-06-02 21:18

GF,2015-06-09 10:13

DF,2015-06-27 15:11

;

run;

data want;

format yearmonth monyy7.;

set have;

year = year(datepart(Invoice_Date));

month = month(datepart(Invoice_Date));

yearmonth = datepart(Invoice_Date);

quarter = qtr(datepart(Invoice_Date));

run;

[mlogan]

Thanks Mark, your code worked. But I want the month as MMM and monthyear as JAN2015. Would you please tell me what would be the code for that.

Thanks,

[Steelers_In_DC]

Do you need the MMM to be in a date format?  I tried using monname. and month. as formats but it is not working for me.  If you use this code it will be a character and not a date.  Also, I changed yearmonth to monthyear but I do not see a problem with the way it was formatted before.  Run this and let me know:

data want;

format monthyear monyy7.;

set have;

_datepart = datepart(invoice_date);

monthyear = _datepart;

year = year(_datepart);

quarter = qtr(_datepart);

month = substr(input(put(monthyear,monyy7.),$7.),1,3);

drop _:;

run;

[mlogan]

Thanks Mark. Actually your previous code was working and I did not notice the yearmonth in my output because it came in the first column (which was at the very left) and the rest came at the very end:).

Regarding MMM, I don't want it that way because it will not retain the date format.

Thanks again for your help Mark.

[mlogan]

Hi Mark, the other day you showed me how to extract yearmonth from datetime format, can you please show me how to extract the yearmonth (eg. Jan2015) from date format (01JAN2015) please. Thanks,

A,2014-02-12

B,2014-04-01

D,2014-05-03

E,2014-07-29

R,2014-09-15

F,2015-03-10

G,2015-06-02

GF,2015-06-09

DF,2015-06-27

[Steelers_In_DC]

You are not extracting anything.  If it's in a date format you are only changing the display.  You have yymmdd10. format and you want monyy7.   See if this helps, run these and take a look at the outputs:

data have;

infile cards dsd;

informat id $2. have want yymmdd10.;

format have yymmdd10. want monyy7.;

input id have want;

cards;

A,2014-02-12,2014-02-12

B,2014-04-01,2014-04-01

D,2014-05-03,2014-05-03

E,2014-07-29,2014-07-29

R,2014-09-15,2014-09-15

F,2015-03-10,2015-03-10

G,2015-06-02,2015-06-02

GF,2015-06-09,2015-06-09

DF,2015-06-27,2015-06-27

;

data want;

format have monyy7.;

set have;

run;

[mlogan]

Thanks Mark, It help.

[AmanSAS]

data one;

input name $2. @5 date1 yymmdd10. time $;

format date1 yymmdd10.;

yr1=year(date1);

mon1=month(date1);

day1=day(date1);

qtr=qtr(date1);

datalines;

A 2014-02-12 20:12

B 2014-04-01 19:00

D 2014-05-03 23:31

E 2014-07-29 17:10

R 2014-09-15 10:31

F 2015-03-10 12:12

G 2015-06-02 21:18

GF 2015-06-09 10:13

DF 2015-06-27 15:11

;

run;