04-22-2017 04:28 PM
Hi I'm trying to merge two files by date. However, the date is not matching and after I ran the code the log showed the following warning: "Variable Date has been defined as both character and numeric"
Here is the example of two files:
Can anyone give some suggestion about how to sovle this problem?
04-22-2017 04:31 PM
"Variable Date has been defined as both character and numeric"
Make them both the same type.
Ideally, SAS Dates, which are numeric with a date format.
You could do this in a join on your SQL condition, but I suggest an intermediate step if you're new to programming and SAS.
Use INPUT to convert to numeric.
04-22-2017 06:22 PM
You can convert the character variable to a date.
data fixed ; set have ; datenum = input(date,yymmdd10.); format datenum yymmdd10.; rename datenum=date date=datechar ; run;
But it is probably best to fix the underlying problem. For example if you used PROC IMPORT to read your CSV files into datasets you could instead just read the CSV directly your self and have control over how the variables are defined.
data cad ; input 'cad.csv' dsd firstobs=2 truncover ; length date cad 8 ; informat date yymmdd.; format date yymmdd10.; input date cad; run; data reutrn; input 'return.csv' dsd firstobs=2 truncover ; length date return 8 ; informat date yymmdd.; format date yymmdd10.; input date return; run;
04-23-2017 12:33 PM
thank you, that's what I thought. However, the log gives the following error:
any suggestion about this problem?
04-23-2017 02:42 PM
Your code is incorrect.
The log tells you the issue:
No datalines or infile statement.
This error has no bearing on your original question however and is an issue with incorrect code.
If you want to manually read your files you need the correct statements:
Please read the entire page, it covers many different scenarios and yours is included.
04-22-2017 06:29 PM
I think this is the same question you posted in a different thread. Regardless, if the "ND" values are in the CAD file, and that is why proc import considered it a character field, here is how I would solve it:
/* The following is simply to replicate what I think you have*/
data cad; informat date $10.; informat cad $6.; input date cad; cards; 2004-01-02 1.29 2004-01-05 1.2803 2004-01-06 1.2824 2004-01-07 1.2824 2004-01-08 1.279 2004-01-09 1.269 ND . 2004-01-12 1.2735 2004-01-13 1.2729 ; data oil; informat date yymmdd10.; input date price; cards; 2004-01-05 33.78 2004-01-06 33.7 2004-01-07 33.62 2004-01-08 33.98 2004-01-09 34.31 ; /* Now here is the proposed solution */
data want_cad (drop=_:); format date date9.; set cad (rename=(date=_date cad=_cad)); date=input(_date, ?? anydtdte11.); cad=input(_cad,8.); if not missing(cad); run; data want; merge want_cad (in=ina) oil (in=inb); by date; if ina and inb; run;
Art, CEO, AnalystFinder.com
04-22-2017 09:22 PM
Thank you art297, I don't quite understand why we need "??" here.
date=input(_date, ?? anydtdte11.);
04-22-2017 09:30 PM
As I recall you had some date fields that contained the characters "ND" or some set of two characters.
The ?? in the input function basically tell SAS to not treat such fields as an error (and stop processing) but, rather, simply set the field to missing and continue processing
Art, CEO, AnalystFinder.com