11-21-2017 10:03 AM
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."
format NewASSERT_DATE date9.;
11-21-2017 10:22 AM
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.
11-21-2017 10:58 AM
Show the data from the dataset:
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.
11-21-2017 03:18 PM - edited 11-21-2017 03:19 PM
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
11-21-2017 05:39 PM
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;
11-21-2017 06:57 PM
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;