- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
What DATE is that file supposed to have in it?
It looks like Independence Day to me. (July 4th).
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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
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.
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;
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).
- « Previous
-
- 1
- 2
- Next »