topic Re: want to merge the two datas by year but error says they are different data types. in SAS Procedures

want to merge the two datas by year but error says they are different data types.

aarony — Thu, 24 Apr 2014 02:31:53 GMT

743 proc sql;

744 create table cshk14 as

745 select a.*, b.*

746 from cshk13 as a, hk21 as b

747 where a.isin = b.isin and a.year = b.annualreportdate;

ERROR: Expression using equals (=) has components that are of different data types.

748 quit;

it seems year in a and annualreportdate in b are different data types. how can i fix this? thank you!!

Re: want to merge the two datas by year but error says they are different data types.

art297 — Thu, 24 Apr 2014 02:56:20 GMT

Both comparisons use the equal operator. Review your data and discover the data types of isin in both datasets, as well as year and annualreportdate. Also, while it may not have shown up as an error you received, look at annualreportdate to see if it is a date or a year.

Re: want to merge the two datas by year but error says they are different data types.

aarony — Thu, 24 Apr 2014 03:01:55 GMT

the annual reportdate is annual reportdate. i think the issue is the data type. do u know how i can check them and also align them?

Re: want to merge the two datas by year but error says they are different data types.

art297 — Thu, 24 Apr 2014 03:13:51 GMT

I would start by running proc contents on both files. e.g.,

proc contents data=cshk13; run;

and

proc contents data=hk21; run;

Re: want to merge the two datas by year but error says they are different data types.

aarony — Thu, 24 Apr 2014 03:17:59 GMT

Thank you arthur for your kind reply.

this is what i get.

AnnualReportDate Char 12 $12. $12.

year Num 8

i am a begginer user in sas (only 1+week of experience) and do not know how to mitigate this issue. could you let me know? thanks much!

Re: want to merge the two datas by year but error says they are different data types.

art297 — Thu, 24 Apr 2014 11:56:42 GMT

You will have to look at the first 10 or so records to see what AnnualReportDate looks like.

I'd run something like the following and let us (the forum) see the result

proc print data=hk21 (obs=10);

var AnnualReportDate;

run;

Re: want to merge the two datas by year but error says they are different data types.

djmangen — Thu, 24 Apr 2014 16:31:45 GMT

Seeing the results of the proc print will be most valuable, but in all likelihood you will need to extract the year from Annual Report Date. Assuming that the annual report date values looks something like 25NOV2013 then I would do something like:

Attrib Report_Year length=8;

AnnualReportDate = right(AnnualReportDate);

Report_Year = substr(AnnualReportDate,9,4);

Re: want to merge the two datas by year but error says they are different data types.

art297 — Thu, 24 Apr 2014 16:43:16 GMT

I'm going to wait making any suggestion until aarony shows us what the data look like. Given that it is a 12 character field I wouldn't assume to know what it looks like. And aarony will need to convert it into a number and will be able to do it all within the original proc sql call.