Building models with SAS Enterprise Miner, SAS Factory Miner, SAS Visual Data Mining and Machine Learning or just with programming

Extracting Month, Year and quarter

Accepted Solution Solved
Reply
Regular Contributor
Posts: 215
Accepted Solution

Extracting Month, Year and quarter

Hi All,

I have the following table with date and time.

NameInvoice_Date
A2014-02-12 20:12
B2014-04-01 19:00
D2014-05-03 23:31
E2014-07-29 17:10
R2014-09-15 10:31
F2015-03-10 12:12
G2015-06-02 21:18
GF2015-06-09 10:13
DF2015-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.

NameInvoice_DateYearMonthYearmonthQuarter
A2014-02-12 20:122014FebFeb-20141
B2014-04-01 19:00
D2014-05-03 23:31
E2014-07-29 17:10
R2014-09-15 10:31
F2015-03-10 12:12
G2015-06-02 21:18
GF2015-06-09 10:13
DF2015-06-27 15:11

Thanks,


Accepted Solutions
Solution
‎07-30-2015 08:29 AM
Valued Guide
Posts: 858

Re: Extracting Month, Year and quarter

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;

View solution in original post


All Replies
Super Contributor
Posts: 307

Re: Extracting Month, Year and quarter

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;

Super User
Super User
Posts: 7,392

Re: Extracting Month, Year and quarter

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.

Occasional Contributor
Posts: 12

Re: Extracting Month, Year and quarter

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;

Regular Contributor
Posts: 215

Re: Extracting Month, Year and quarter

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,

Super User
Super User
Posts: 7,392

Re: Extracting Month, Year and quarter

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;

Solution
‎07-30-2015 08:29 AM
Valued Guide
Posts: 858

Re: Extracting Month, Year and quarter

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;

Regular Contributor
Posts: 215

Re: Extracting Month, Year and quarter

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,

Valued Guide
Posts: 858

Re: Extracting Month, Year and quarter

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;

Regular Contributor
Posts: 215

Re: Extracting Month, Year and quarter

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 endSmiley Happy.

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

Thanks again for your help Mark.

Regular Contributor
Posts: 215

Re: Extracting Month, Year and quarter

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

Valued Guide
Posts: 858

Re: Extracting Month, Year and quarter

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;

Regular Contributor
Posts: 215

Re: Extracting Month, Year and quarter

Thanks Mark, It help.

New Contributor
Posts: 2

Re: Extracting Month, Year and quarter

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 803 views
  • 7 likes
  • 6 in conversation