DATA Step, Macro, Functions and more

date manipulation from SAS datetime field

Reply
Occasional Contributor
Posts: 10

date manipulation from SAS datetime field

[ Edited ]

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

Contributor
Posts: 21

Re: date manipulation from SAS datetime field

format date ddmmyy10.
Occasional Contributor
Posts: 10

Re: date manipulation from SAS datetime field

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

Super User
Posts: 5,080

Re: date manipulation from SAS datetime field

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.

Contributor
Posts: 44

Re: date manipulation from SAS datetime field

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
Occasional Contributor
Posts: 10

Re: date manipulation from SAS datetime field

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

Contributor
Posts: 44

Re: date manipulation from SAS datetime field

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!

Super User
Posts: 10,483

Re: date manipulation from SAS datetime field

[ Edited ]

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).

 

Ask a Question
Discussion stats
  • 7 replies
  • 151 views
  • 2 likes
  • 5 in conversation