SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

problem with coding data variable as numeric

Reply
Contributor
Posts: 25

problem with coding data variable as numeric

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!

 

 

Trusted Advisor
Posts: 1,977

Re: problem with coding data variable as numeric

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

--
Paige Miller
Contributor
Posts: 25

Re: problem with coding data variable as numeric

Posted in reply to PaigeMiller

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
Super User
Super User
Posts: 8,174

Re: problem with coding data variable as numeric

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. 

Super User
Super User
Posts: 7,154

Re: problem with coding data variable as numeric

[ Edited ]

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

 

Contributor
Posts: 25

Re: problem with coding data variable as numeric

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;
Super User
Super User
Posts: 7,154

Re: problem with coding data variable as numeric

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;

 

Ask a Question
Discussion stats
  • 6 replies
  • 105 views
  • 0 likes
  • 4 in conversation