BookmarkSubscribeRSS Feed
akhileshJoshi
Calcite | Level 5

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

8 REPLIES 8
gauthamk28
Obsidian | Level 7
format date ddmmyy10.
akhileshJoshi
Calcite | Level 5

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

biopharma
Quartz | Level 8

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
Astounding
PROC Star

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.

JasonDiVirgilio
Quartz | Level 8

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

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

JasonDiVirgilio
Quartz | Level 8

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!

ballardw
Super User

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

 

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1172 views
  • 2 likes
  • 6 in conversation