DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 49

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

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:

CAD (2).pngOil_return.png

Can anyone give some suggestion about how to sovle this problem?

Thank you.

Super User
Posts: 19,810

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


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.

Contributor
Posts: 49

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

Thank you Reeza, I will try this

Super User
Super User
Posts: 7,050

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

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;
Contributor
Posts: 49

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

Thkank you, I don't quite understand the 

'cad.csv' 

Should this be the path?  

Super User
Super User
Posts: 7,050

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

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

Contributor
Posts: 49

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

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

any suggestion about this problem?

Super User
Posts: 19,810

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

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.

 

 

Contributor
Posts: 49

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

Thank you, I'll read it

PROC Star
Posts: 7,474

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

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

Contributor
Posts: 49

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

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

 date=input(_date, ?? anydtdte11.);
PROC Star
Posts: 7,474

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

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

 

Contributor
Posts: 49

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

Thank you art297, I understand now.

Ask a Question
Discussion stats
  • 12 replies
  • 225 views
  • 0 likes
  • 4 in conversation