topic Re: Merge by date, variable Date has been defined as both character and numeric in SAS Programming

Merge by date, variable Date has been defined as both character and numeric

Xusheng — Sat, 22 Apr 2017 20:28:54 GMT

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.

Re: Merge by date, variable Date has been defined as both character and numeric

Reeza — Sat, 22 Apr 2017 20:31:07 GMT

@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.

Re: Merge by date, variable Date has been defined as both character and numeric

Tom — Sat, 22 Apr 2017 22:22:42 GMT

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;

Re: Merge by date, variable Date has been defined as both character and numeric

art297 — Sat, 22 Apr 2017 22:29:39 GMT

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

Re: Merge by date, variable Date has been defined as both character and numeric

Xusheng — Sat, 22 Apr 2017 23:46:19 GMT

Thank you Reeza, I will try this

Re: Merge by date, variable Date has been defined as both character and numeric

Xusheng — Sun, 23 Apr 2017 01:19:48 GMT

Thkank you, I don't quite understand the

'cad.csv'

Should this be the path?

Re: Merge by date, variable Date has been defined as both character and numeric

Xusheng — Sun, 23 Apr 2017 01:22:02 GMT

Thank you art297, I don't quite understand why we need "??" here.

 date=input(_date, ?? anydtdte11.);

Re: Merge by date, variable Date has been defined as both character and numeric

art297 — Sun, 23 Apr 2017 01:30:40 GMT

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

Re: Merge by date, variable Date has been defined as both character and numeric

Xusheng — Sun, 23 Apr 2017 16:16:15 GMT

Thank you art297, I understand now.

Re: Merge by date, variable Date has been defined as both character and numeric

Tom — Sun, 23 Apr 2017 16:18:12 GMT

Yes. You need to tell the INPUT statement where to find your data file.

Re: Merge by date, variable Date has been defined as both character and numeric

Xusheng — Sun, 23 Apr 2017 16:33:19 GMT

thank you, that's what I thought. However, the log gives the following error:

any suggestion about this problem?

Re: Merge by date, variable Date has been defined as both character and numeric

Reeza — Sun, 23 Apr 2017 18:42:43 GMT

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.

Re: Merge by date, variable Date has been defined as both character and numeric

Xusheng — Sun, 23 Apr 2017 19:51:27 GMT

Thank you, I'll read it