topic Re: Extracting Month, Year and quarter in SAS Data Science

Extracting Month, Year and quarter

mlogan — Wed, 29 Jul 2015 21:50:33 GMT

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,

Re: Extracting Month, Year and quarter

Fugue — Thu, 30 Jul 2015 01:31:41 GMT

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;

Re: Extracting Month, Year and quarter

gcleggs — Thu, 30 Jul 2015 08:15:55 GMT

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;

Re: Extracting Month, Year and quarter

RW9 — Thu, 30 Jul 2015 08:47:26 GMT

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;

Re: Extracting Month, Year and quarter

RW9 — Thu, 30 Jul 2015 08:48:58 GMT

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.

Re: Extracting Month, Year and quarter

Steelers_In_DC — Thu, 30 Jul 2015 12:29:27 GMT

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;

Re: Extracting Month, Year and quarter

mlogan — Thu, 30 Jul 2015 13:43:44 GMT

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,

Re: Extracting Month, Year and quarter

mlogan — Thu, 30 Jul 2015 13:51:11 GMT

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,

Re: Extracting Month, Year and quarter

Steelers_In_DC — Thu, 30 Jul 2015 14:00:47 GMT

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;

Re: Extracting Month, Year and quarter

mlogan — Thu, 30 Jul 2015 14:17:20 GMT

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.

Re: Extracting Month, Year and quarter

AmanSAS — Fri, 31 Jul 2015 02:16:54 GMT

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;

Re: Extracting Month, Year and quarter

mlogan — Tue, 11 Aug 2015 16:43:02 GMT

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

Re: Extracting Month, Year and quarter

Steelers_In_DC — Tue, 11 Aug 2015 16:54:08 GMT

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;

Re: Extracting Month, Year and quarter

mlogan — Tue, 11 Aug 2015 18:22:48 GMT

Thanks Mark, It help.