topic formatting nlstrmon. as character in New SAS User

formatting nlstrmon. as character

hnb_matt_d — Thu, 27 Feb 2020 19:11:20 GMT

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

Re: formatting nlstrmon. as character

Tom — Thu, 27 Feb 2020 19:20:47 GMT

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?

Re: formatting nlstrmon. as character

Reeza — Thu, 27 Feb 2020 19:28:26 GMT

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

put(table.field, monname.) as Month

Re: formatting nlstrmon. as character

hnb_matt_d — Thu, 27 Feb 2020 20:15:32 GMT

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