problem with coding data variable as numeric

[lmyers2]

Hello,

I am using SAS 9.4 full edition. I have dates being read in from an excel file as a character variable but I need them to be numeric so I can manipulate them more easily and make new variables that are the time between two dates. For some reason, the following code works for one data set (cases) but not for the other (controls). The error messages I get are: "character values have been converted to numeric values. Variable date9 is uninitialized. Invalid numeric data ASSERT_DATE."

NewASSERT_DATE=input(ASSERT_DATE, yymmdd10.);

format NewASSERT_DATE date9.;

Thanks!

[PaigeMiller]

Show us examples of the input data where the code works, and examples of the input data where the code does not work.

[lmyers2]

Here are the data steps for each. I know the first line about length is redundant but I don't think it's the reason why it's not working for one file.

CODE FOR CASES WHICH WORKS

data CBScasefile; set CBScasefile;
length NewASSERT_DATE 8;
format NewASSERT_DATE date9.;
NewASSERT_DATE=input(ASSERT_DATE, yymmdd10.);
run; 


CODE FOR CONTROLS WHICH DOESN'T WORK

data CBScontrol file; set CBScontrolfile;
length NewASSERT_DATE 8;
format NewASSERT_DATE date9.;
NewASSERT_DATE=input(ASSERT_DATE, yymmdd10.);
run;

In the excel file that SAS is reading from, here is how a sample date is formatted n each file.

CASE

2013-09-06

CONTROL

2003-01-20

[RW9]

Show the data from the dataset: 

CBScontrolfile

Put this as a datastep in a code window.  What is likely as you are importing from Excel (which is a poor data medium) and probably using proc import (a guessing procedure) which results most of the time in garbage out, which can mean that assert_date in the second example does not look at all like it does in the first.  Also show the log of the code working as this always tells you the issue. 

[Tom]

The original error messages you posted (without any context) include some that made it look like you might have submitted some code with missing semi-colons or perhaps missing periods in format specifications

Also pictures of what a cell looks like in Excel is not always a good indication of what type of data is in the cell, or how SAS might try to convert it. 

Similarly in SAS how SAS prints a value depends on the format that uses.  So you might have a character string with '2003-01-20' in it, but you might also have a numeric variable with '20JAN2003'd in it and the YYMMDD10. format attached to it.  Both will look the same when printed or browsed in some GUI tool.

If you have an actual DATE variable in SAS then you can just just change the format attached to it to get it to display correctly..

1471  data test;
1472    chardate='2003-01-20';
1473    numdate='20JAN2003'd;
1474    format numdate yymmdd10.;
1475
1476    newdate1=numdate;
1477    format newdate1 date9.;
1478    newdate2=input(chardate,yymmdd10.);
1479    format newdate2 date9.;
1480
1481    put (_all_) (= /);
1482  run;


chardate=2003-01-20
numdate=2003-01-20
newdate1=20JAN2003
newdate2=20JAN2003

[lmyers2]

Thanks so much for looking at the code. Below is the proc import statement and data step, along with the proc contents result showing ASSERT_DATE as a numeric variable, and the error messages; they are all in the attachment if that is easier to read. 

proc import
datafile='location'
dbms=xlsx replace
out=CBScontrolfile;
sheet='case'; 
getnames=yes;
run;

proc sort data=CBScontrolfile; by CASE_ID; run;


data CBScontrolfile; set CBScontrolfile; 

length NewASSERT_DATE 8;
NewASSERT_DATE=input(ASSERT_DATE, yymmdd10.);
format NewASSERT_DATE date9;
run;

proc contents position; run;

[Tom]

Not sure why you are able to post the text of the SAS program, but could only post a photograph of the text in the SAS log.

SAS has to guess as to what type of variables to create when you use PROC IMPORT.  You need to run PROC CONTENTS on the dataset that you create with PROC IMPORT. That will tell you the type, length and any format that is attached to the variables.  

The error messages in the log make it look like ASSERT_DATE is indeed text and does seem to have valid dates.  Put perhaps those are not really hyphens or not really zeros.  Or perhaps the values actually include the quotes?  If you post the text from the log then you can check it.

You could also add a diagnostic step in your data step to dump out the values when the input() function doesn't work.

data CBScontrolfile;
  set CBScontrolfile; 
  length NewASSERT_DATE 8;
  NewASSERT_DATE=input(ASSERT_DATE, yymmdd10.);
  format NewASSERT_DATE date9;
  if missing(newassert_date) and not missing(assert_date) then put ASSERT_DATE = :$quote. ;
run;