Administration and Deployment

Installing and maintaining your SAS environment
BookmarkSubscribeRSS Feed
Tom
Super User Tom
Super User

What DATE is that file supposed to have in it?

It looks like Independence Day to me.  (July 4th).

Tom_0-1733590534878.png

 

Is your issue that SAS is displaying it as July Fourth (07/04/2023) and you want it displayed as the Fourth of July (04/07/2023).   If so then just change the FORMAT attached to the variable.

 

But if you meant for the file to have the Seventh of April instead of July Fourth then your problem is in the process that is making the XLSX file.  My earlier post showed SAS pulls over the actual DATE that Excel has stored, no matter what style Excel is using to display it in the worksheet.

 

Patrick
Opal | Level 21
@freshstarter wrote:

Attached here the input file.

 

The date in the excel file is 02/08/2023 ( which is 02nd Aug 2023 ) . Our final result expected to be the same in another csv file. This is how our workflow created



@freshstarter  The date in the Excel you attached is 4th of July and not as you write 02/08/2023

Patrick_0-1733618108702.png

 

Proc Import will use the Excel cell Format Code to convert the cell value into a SAS date.

SAS stores dates in a numerical variable as the count of days since 01Jan1960. SAS date formats make such counts human readable as date strings.

 

Proc Contents will tell you what format Proc Import attached to the variable.

 

Proc Export will use this format to write strings to a .csv (which is just a text file). For example if the format is date9. then the SAS count of days since 01Jan1960 will get written as a date string of the form ddmonyyyy

 

I've attached an Excel to demonstrate what I'm talking about.

Patrick_1-1733618946040.png

 

Even though the start dates in the two data rows look the same they will create different SAS date values when imported into SAS. The reason is that they've got different cell formats (format code). 

 

You can see for yourself if running below code using the attached Excel.

%let root_path=/home/&sysuserid;

PROC IMPORT DATAFILE="&root_path/temp/input_excelfile_gb_us.xlsx"
	DBMS=XLSX
	OUT=WORK.excel_gb_us
	replace;
	GETNAMES=YES;
RUN;

proc sql;
	select 
		 format_cells_language
		,format_cells_category
		,format_cells_format
		,format_cells_formatCode
		,start_date format=best32.   label='start_date format=best32.'
		,start_date format=date9.    label='start_date format=date9'
		,start_date format=ddmmyy10. label='start_date format=ddmmyy10.'
		,start_date format=mmddyy10. label='start_date format=mmddyy10.'
	from WORK.excel_gb_us
	;
quit;

Patrick_2-1733619176632.png

The highlighted column above is showing how SAS actually stores the date values (just count of days since 1/1/1960 as a number). The other columns then show how SAS prints these numbers using some of the available date formats. And it's the same when writing to a .csv. It's just the format that "decides" how the values will get written.

 

And last but not least:

Similar to SAS also Excel stores dates as the count of days since a start date. With Excel day one is 01Jan1900. The Excel cell format then "decides" how the date displays in the spreadsheet. SAS needs only to know that the cell value is a date and then it's a simple formula to convert the Excel day count to the SAS day count (...with some minor challenge for 1900 where Microsoft wrongly assumed it's a leap year).

 

 

 

 

 

 

 

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

Get Started with SAS Information Catalog in SAS Viya

SAS technical trainer Erin Winters shows you how to explore assets, create new data discovery agents, schedule data discovery agents, and much more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 17 replies
  • 2188 views
  • 16 likes
  • 6 in conversation