Summary of the problem:
Trying to read a CSV file generated by an automated process containing an ID and date field. When the CSV file was opened in Excel, the data displayed something like this:
ID
Date
100
1-Sep
100
2-Sep
100
3-Sep
If you click on the date in the first cell, Excel displays 9/1/2023 as the actual value
It turns out that Excel is interpreting the data, not showing that data as it actually resides in the raw file. This isn't helpful. Instead, as recommended by @ballardw and @Tom, we should open the CSV file in a text editor like Notepad++ (or even the SAS program editor) to see the raw data as it actually is:
"ID","Date"
100,"09-01"
100,"09-02"
100,"09-03"
We can see that the file only provides month and day values for the date. We know that this file was generated by the system in the year 2023. When we read the file in SAS, we'll use SCAN to extract the Month and Day values as text, INPUT to convert the text values to numeric, and finally MDY to produce a SAS date value.
/* Creat a sample CSV file */
filename haveCSV temp;
data _null_;
file haveCSV ;
length line $15;
if _n_=1 then do;
put '"ID","Date"';
end;
do ID=100 to 102;
do day=1 to 5;
line=catx(',',ID,'"'||catx('-','09',put(day,z2.))||'"');
put line ;
end;
end;
run;
/* View the raw CSV file contents in the Log*/
data _null_;
infile haveCSV ;
input;
put _infile_;
run;
/* Read the CSV file into SAS */
data want;
infile haveCSV dsd missover firstobs=2;
length Assignment_ID $3 Date 8;
format Date YYMMDD10.;
input Assignment_ID _txtDate:$15.;
Date=mdy(input(scan(_txtDate,1,'-'),32.)
,input(scan(_txtDate,2,'-'),32.)
,2023);
drop _:;
run;
Here's what the data looked like in the log:
"ID","Date"
100,"09-01"
100,"09-02"
100,"09-03"
100,"09-04"
100,"09-05"
101,"09-01"
101,"09-02"
101,"09-03"
101,"09-04"
101,"09-05"
102,"09-01"
102,"09-02"
102,"09-03"
102,"09-04"
102,"09-05"
And the resulting SAS dataset:
Obs
Assignment_ID
Date
1
100
2023-09-01
2
100
2023-09-02
3
100
2023-09-03
4
100
2023-09-04
5
100
2023-09-05
6
101
2023-09-01
7
101
2023-09-02
8
101
2023-09-03
9
101
2023-09-04
10
101
2023-09-05
11
102
2023-09-01
12
102
2023-09-02
13
102
2023-09-03
14
102
2023-09-04
15
102
2023-09-05
... View more
