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?
Thank you.
@Xusheng wrote:
"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.
Thank you Reeza, I will try this
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;
Thkank you, I don't quite understand the
'cad.csv'
Should this be the path?
Yes. You need to tell the INPUT statement where to find your data file.
thank you, that's what I thought. However, the log gives the following error:
any suggestion about this problem?
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:
http://stats.idre.ucla.edu/sas/faq/how-do-i-read-in-a-delimited-ascii-file-in-sas/
Please read the entire page, it covers many different scenarios and yours is included.
Thank you, I'll read it
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
Thank you art297, I don't quite understand why we need "??" here.
date=input(_date, ?? anydtdte11.);
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
Thank you art297, I understand now.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.