BookmarkSubscribeRSS Feed
Xusheng
Obsidian | Level 7

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.

12 REPLIES 12
Reeza
Super User

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

Xusheng
Obsidian | Level 7

Thank you Reeza, I will try this

Tom
Super User Tom
Super User

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;
Xusheng
Obsidian | Level 7

Thkank you, I don't quite understand the 

'cad.csv' 

Should this be the path?  

Tom
Super User Tom
Super User

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

Xusheng
Obsidian | Level 7

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

any suggestion about this problem?

Reeza
Super User

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.

 

 

art297
Opal | Level 21

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

Xusheng
Obsidian | Level 7

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

 date=input(_date, ?? anydtdte11.);
art297
Opal | Level 21

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

 

Xusheng
Obsidian | Level 7

Thank you art297, I understand now.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 1517 views
  • 0 likes
  • 4 in conversation