topic Re: date in SAS Programming

date

Dhana18 — Thu, 05 Dec 2019 16:51:22 GMT

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.

Re: date

Shakir_Juolay — Thu, 05 Dec 2019 17:47:48 GMT

Please share output of the below
1) PROC CONTENTS DATA=ALERTCSE.Alert_Cases; RUN;
2) PROC PRINT DATA=ALERTCSE.Alert_Cases (OBS=10); RUN;

Re: date

PaigeMiller — Thu, 05 Dec 2019 18:08:00 GMT

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;

Re: date

Tom — Thu, 05 Dec 2019 19:48:56 GMT

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;