The following is a macro I wrote to left out join two datasets.
/*
* Macro to add the t-rate to the data
*/
%MACRO RFRATE(WITHOUT_RATE, WITH_RATE);
/*read the tbill rate data*/
data TBRATE;
INFILE "T-RATE/T-RATE-FORMATTED.csv"
DELIMITER = ","
MISSOVER DSD
FIRSTOBS=2
LRECL=32767;
INPUT DATE one_M three_M six_M one_Y two_Y three_Y five_Y seven_Y ten_Y twenty_Y thirty_Y;
format DATE yymmddn8.;
length Date 4;
run;
data &WITH_RATE;
merge &WITHOUT_RATE(IN=A) TBRATE(IN=B);
by Date;
if A;
run;
/*I have also tried the following way (SQL)*/
/*
PROC SQL;
Create table &WITH_RATE as
Select a.*,b.*
from
&WITHOUT_RATE a
left join
TBRATE b
on a.Date=b.Date;
quit;
*/
proc export data=&WITH_RATE (obs=99999)
outfile= 'samplesmall.csv'
dbms=CSV REPLACE;
putname=YES;
run;
proc contents data= TBRATE position;
run;
proc contents data= &WITHOUT_RATE position;
run;
%mend;
The TBRATE is some data similar to the following format:
| Date | Rate |
|----------|----------|
| 20120101 | 1.0 |
| 20120102 | 1.5 |
| 20120103 | 1.5 |
| 20120104 | 1.3 |
| 20120105 | 1.1 |
The WITHOUT_RATE is some data similar to the following:
| Date | Other Data |
|----------|---------------|
| 20120101 | 7.0 |
| 20120101 | 3.5 |
| 20120101 | 4.5 |
| 20120101 | 2.3 |
| 20120101 | 11.1 |
| 20120102 | 23.0 |
| 20120102 | 12.5 |
| 20120102 | 12.5 |
| 20120102 | 11.3 |
| 20120102 | 11.1 |
I wanted to create something similar to the following:
| Date | Other Data | Rate |
|----------|---------------|----------
| 20120101 | 7.0 | 1.0 |
| 20120101 | 3.5 | 1.0 |
| 20120101 | 4.5 | 1.0 |
| 20120101 | 2.3 | 1.0 |
| 20120101 | 11.1 | 1.0 |
| 20120102 | 23.0 | 1.5 |
| 20120102 | 12.5 | 1.5 |
| 20120102 | 12.5 | 1.5 |
| 20120102 | 11.3 | 1.5 |
| 20120102 | 11.1 | 1.5 |
The format of the date variable in WITHOUT_RATE is:
Variable Type Len Format Label
DATE Num 4 YYMMDDN8. Quote date
The format of the date variable in TBRATE is:
Variable Type Len Format
DATE Num 4 YYMMDDN8.
It seems that they are the same.
However, the code keep producing null results:
| Date | Other Data | Rate |
|----------|---------------|----------
| 20120101 | 7.0 | |
| 20120101 | 3.5 | |
| 20120101 | 4.5 | |
| 20120101 | 2.3 | |
| 20120101 | 11.1 | |
| 20120102 | 23.0 | |
| 20120102 | 12.5 | |
| 20120102 | 12.5 | |
| 20120102 | 11.3 | |
| 20120102 | 11.1 | |
Therefore, I was wondering where did I go wrong?
Update #2
The code used to call the macro:
/*The output dataset*/
%NBBO(20130102, tempoutputset);
%NBBOReturn(tempoutputset, NBBODATA);
%RFRATE(NBBODATA, RFRATEDATA);
I solved it. Thanks to @Quentin and @India.Rocket's suggestions, I was able to find the solution.
The problem was reading the dates from csv file. It seems that dates in the format of yyyymmdd or yyyy-mm-dd cannot be read correctly. I had to reformat the dates in the csv to mm/dd/yyyy with python, then input them as DATE:mmddyy10 in sas. However, the other dataset I was merging with has the date format in yymmddn8. Therefore, I had to reformat DATE:mmddyy10. to yymmddn8. to make the whole thing work.
Following code was used.
data TBRATE;
INFILE "T-RATE/T-RATE-FORMATTED.csv"
DELIMITER = ","
MISSOVER DSD
FIRSTOBS=2
LRECL=32767;
INPUT DATE:mmddyy10. DateRaw one_M three_M six_M one_Y two_Y three_Y five_Y seven_Y ten_Y twenty_Y thirty_Y;
format DATE yymmddn8.;
run;