I had TOC date in date format in excel. after imported in to SAS it became like this
TOC | After imported in SAS |
7/17/2017 | 42933 |
7/24/2017 | 42940 |
After imported it is in character format, I used this code to change it to numeric date format
DATA ALERTCSE.Alert_Cases_TOC;
SET ALERTCSE.Alert_Cases;
format Test_of_Cure mmddyy10.;
TOC = INPUT (TOC, mmddyy10.);
drop TOC;
rename Test_of_Cure=TOC;
run;
and it says this;
NOTE: Mathematical operations could not be performed at the following places. The results of the
operations have been set to missing values.
Each place is given by: (Number of times) at (Line):(Column).
85 at 193:16
NOTE: There were 166 observations read from the data set ALERTCSE.ALERT_CASES.
NOTE: The data set ALERTCSE.ALERT_CASES_TOC has 166 observations and 36 variables.
NOTE: DATA statement used (Total process time):
real time 0.21 seconds
cpu time 0.14 seconds
And TOC column is with these dates which is WRONG
TOC |
4/29/1933 |
4/29/1940 |
Please help me.
To convert Excel dates to SAS dates, subtract 21916.
You also need the dates from Excel to show up as numeric integers, I'm not sure why that didn't happen, as you don't show your code; but I'm sure you can figure it out.
So, to see that this works, here's an example:
data want;
y=42933-21916;
y1=42940-21916;
format y y1 date7.;
run;
If you have mixed type cells (numeric and character) in the same column of your spreadsheet then SAS will define the variable as character. For DATE values that are in that column SAS will store the value as the character representation of the number that Excel uses for dates. To fix it make sure the Excel sheet has only one type of data in that column. Then SAS will know it is a date and do the conversion for you.
If you cannot fix the Excel sheet then you will have to do something in SAS. So convert the value to a number than change the value to reflect difference in how SAS and Excel count days.
Sometimes Excel gets confused by strings that look like dates to humans, but not to Excel and will store those as a character string.
Here is code that will first try to convert the TOC value to a number. If it doesn't work it will then try to convert it using a specific date informat. If it does work then it will add the SAS date for 2 day before 1900. SAS uses 1960 as the start and Excel uses 1900. But they made different decisions about whether to count from zero or one and Excel mistakenly thinks 1900 was a leap year.
data ALERTCSE.Alert_Cases_TOC;
set ALERTCSE.Alert_Cases;
format Test_of_Cure mmddyy10.;
test_of_cure = input(toc,??32.);
if missing(test_of_cure) and not missing(toc) then do;
test_of_cure=input(toc,mmddyy10.);
end;
else do;
test_of_cure = test_of_cure + '30DEC1899'd ;
end;
rename TOC=TOC_char ;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.