I don't know how you imported your data. I used proc import, using the dbms=xlsx option.
Using that method, the company IDs were character in one file, numeric in the other.
Similarly, the dates in one file were numbers rather than dates and, in the other, were quarters represented as a character variable.
Here is the code I ran to solve your problem of merging the two files:
proc import datafile="/folders/myfolders/first_database.xlsx"
out=first_database replace dbms=xlsx;
getnames=yes;
mixed=yes;
sheet='Sheet1';
usedate=yes;
scantime=yes;
run;
proc import datafile="/folders/myfolders/second database.xlsx"
out=second_database replace dbms=xlsx;
getnames=yes;
mixed=yes;
sheet='Sheet1';
usedate=yes;
scantime=yes;
run;
data first_database (drop=_:);
set first_database (rename=(
fiscal_data_year_and_quarter=
_fiscal_data_year_and_quarter));
format fiscal_data_year_date date9.;
fiscal_data_year_date=
mdy(substr(_fiscal_data_year_and_quarter,6,1)*3,1,
substr(_fiscal_data_year_and_quarter,1,4));
fiscal_data_year_and_quarter=put(fiscal_data_year_date,yyq6.);
run;
proc sort data=first_database;
by global_company_key;
run;
options datestyle=YMD;
data second_database (drop=_:);
set second_database (rename=(
announcedate=
_announcedate
global_company_key=_global_company_key));
format announcedate date9.;
announcedate=
input(strip(_announcedate),anydtdte8.);
global_company_key=input(_global_company_key,8.);
run;
proc sort data=second_database;
by global_company_key;
run;
data want;
merge first_database second_database;
by global_company_key;
run;
HTH,
Art, CEO, AnalystFinder.com
... View more
