BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hnb_matt_d
Obsidian | Level 7

Hello! As part of a create table select proc sql, I am utilizing nlstrmon. to return the month name from a datetime20. formatted field.  The output data shows the month name, however it is formatted as a numeric field 8 bytes in length.  When exported to Excel, the month name converts back to the corresponding month number.  Any suggestions for changing the formatting to character? 

 

proc sql;			
			
	create table work.XXXX as		
	select 	month(table.field) format=nlstrmon. as MONTH
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
Try the MONNAME format instead? You can use the date with that function directly.

put(table.field, monname.) as Month

View solution in original post

3 REPLIES 3
Tom
Super User Tom
Super User

You using confusing terminology. A format is an attribute of a variable.  SAS has just two variable TYPEs, either floating point numbers or a fixed length character strings.

  

It looks like you are currently converting a date value (number of days since 1960) into a month value (integer between 1 and 12) by using the MONTH() function. Then attached a format that show that number as a month name.  

Sounds like you would prefer to create a character variable instead.  Use the PUT() function to get the result of display the value using a given format specification.

 

put(month(table.field),nlstrmon.) as MONTH

A format is instructions for converting values into text.  You can attach them to a variable (as in your original code) and SAS will use them as the default way to display the values.  But if you use PROC EXPORT or XLSX libname engine to convert your data into an EXCEL it will copy the value, not how it is displayed.  For some key formats, like date, time and datetime, SAS tries to convert the value to a matching value in Excel and attach an appropriate display format to the cell.  But it does not know how to map every possible format into an Excel equivalent.  Is there even an Excel equivalent that displays 1 as January?

 

Reeza
Super User
Try the MONNAME format instead? You can use the date with that function directly.

put(table.field, monname.) as Month
hnb_matt_d
Obsidian | Level 7

Thank you, Tom and Reeza, for replying! 

 

Both produced output, but not proper results.  Building on what Reeza suggested, I've done this and it produced desired results and exported to Excel as desired:

 

select 	
	put(datepart(table.field), monname.) as MONTH

Thank you both so much for the quick replies! 

 

Matt D

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 906 views
  • 2 likes
  • 3 in conversation