BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mlogan
Lapis Lazuli | Level 10

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,

1 ACCEPTED SOLUTION

Accepted Solutions
Steelers_In_DC
Barite | Level 11

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

13 REPLIES 13
Fugue
Quartz | Level 8

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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
Lapis Lazuli | Level 10

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Barite | Level 11

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
Lapis Lazuli | Level 10

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
Barite | Level 11

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
Lapis Lazuli | Level 10

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
Lapis Lazuli | Level 10

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
Barite | Level 11

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
Lapis Lazuli | Level 10

Thanks Mark, It help.

AmanSAS
Calcite | Level 5

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to choose a machine learning algorithm

Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 13 replies
  • 20655 views
  • 8 likes
  • 6 in conversation