topic Re: date manipulation from SAS datetime field in SAS Programming

date manipulation from SAS datetime field

akhileshJoshi — Thu, 18 May 2017 19:34:52 GMT

Hi All,

I am trying to get a format where for each month of a year, I would have a specific value. For example, I would 2nd January and 3rd February 2017 to be displayed as 01/01/2017 and 02/01/2017. Basically, irrespective of the actual date of month, I would like the date to be 1st of every month while the month number changes. Using

Data constants;

date = datepart (datetime);

run;

proc print data=constants;

format date mmddyy10.

run;

I get 4th January 2017 as 01/04/2017, but I want it to be 01/01/2017. I.e. for any date in January, the output should be 01/01/2017. How can I achieve it? Is there any format for this? Would greatly appreciate your help.

Thanks and regards,

Akhilesh

Re: date manipulation from SAS datetime field

gauthamk28 — Thu, 18 May 2017 19:44:03 GMT

format date ddmmyy10.

Re: date manipulation from SAS datetime field

Astounding — Thu, 18 May 2017 19:48:19 GMT

Why not use a format that gives the right impression:

format date yymmd7.;

That way, nobody will complain about representing different days as the "first" day of the month.

Re: date manipulation from SAS datetime field

JasonDiVirgilio — Thu, 18 May 2017 19:53:02 GMT

I don't know of a format that would do it, but the INTNX function can do it:

data test;
mydate = mdy(1,4,2017);
newdate = intnx('month', mydate, 0, 'B');
put mydate= mmddyy10. newdate= mmddyy10.;
run;

log:

mydate=01/04/2017 newdate=01/01/2017

Re: date manipulation from SAS datetime field

akhileshJoshi — Thu, 18 May 2017 20:10:16 GMT

That doesn't work Gautam. As I said, irrespective of the date being anything between 1 to 31, I want it to be 01.

Example:

3rd January 2017 should be 01/01/2017.

4th February 2017 should be 02/01/2017

8th March 2017 should be 03/01/2017.

Thanks and regards,

Akhilesh

Re: date manipulation from SAS datetime field

akhileshJoshi — Thu, 18 May 2017 20:16:47 GMT

Hi Jason,

The field from which I am trying to extract the values is a date field with format DATETIME22.3. It doesn't accept the mdy arguement you mentioned.

Thanks and regards,

Akhilesh

Re: date manipulation from SAS datetime field

JasonDiVirgilio — Thu, 18 May 2017 20:22:55 GMT

That's fine..I was just trying to show you an example of how to use the INTNX function to do what you wanted:

Using:

intnx('month', mydate, 0, 'B');

...where "mydate" is your date variable should give a date that is the beginning of the same month/year.

Good luck!

Re: date manipulation from SAS datetime field

ballardw — Thu, 18 May 2017 20:59:09 GMT

@akhileshJoshi wrote:

Hi Jason,

The field from which I am trying to extract the values is a date field with format DATETIME22.3. It doesn't accept the mdy arguement you mentioned.

Thanks and regards,

Akhilesh

In SAS terminology that is very poor statement. Dates are a count of days since 1 Jan 1960. Datetimes are a count of Seconds. If you mix the terminology you will get poor recommendations.

When it comes to custom appearances then custom formats come into play. The Picture statement in Proc Format will allow you access all the components of a displayed variable in the way you want.

proc format library=work;
picture dtfixed (default=10)
low-high = '%0m/01/%Y'  (datatype=datetime)
;
run;

data _null_;
   dt = '23JAN2017:12:15:23'dt;
   put dt dtfixed.;
run;

will display that datetime value as 01/01/2017 (or any datetime as the first day of the month).

Re: date manipulation from SAS datetime field

biopharma — Sat, 30 May 2020 02:59:09 GMT

You need the INTNX function and increment by 0 months but advance it to the beginning of the month.

data have ;
   input havedate:mmddyy10. ;
   cards ;
01/04/2017
02/01/2017
04/20/2017
;
run ;

data want ;
   set have ;
   format havedate wantdate mmddyy10. ;
   wantdate = intnx("month",havedate,0,"b") ;
run ;

proc print ;
run ;

Obs	havedate	wantdate
1	01/04/2017	01/01/2017
2	02/01/2017	02/01/2017
3	04/20/2017	04/01/2017