I have two files.
File 1 has (permno, mend) as columns. Mend is a variable that is the end of calendar month (03/31/2017).
File 2 has (companyid price and date) as columns. prices and dates are daily.
I am trying to merge both files but from file 2 I need the price at the end of the month. If it is not available, I will need the last available traded price for that month. I have a sample code for the last 30 days but I think my code is wrong. Could you please help.
data have1;
infile datalines dsd truncover;
input PERMNO:8. MEND:DATE9.;
format PERMNO:8. MEND DATE9.;
datalines4;
10107,30SEP2006
10107,31OCT2006
10107,30NOV2006
10107,31DEC2006
;;;;
data have2;
infile datalines dsd truncover;
input PERMNO:8. DATE:YYMMDDN8. PRC:12.5;
format PERMNO 8. DATE YYMMDDN8. PRC 12.5;
datalines4;
10107,20060901,25.84000
10107,20060905,25.61000
10107,20060906,25.61000
10107,20060907,25.43000
10107,20060908,25.60000
10107,20060911,25.91000
10107,20060912,25.93000
10107,20060913,25.98000
10107,20060914,26.33000
10107,20060915,26.85000
10107,20060918,26.79000
10107,20060919,26.86000
10107,20060920,27.18000
10107,20060921,26.90000
10107,20060922,26.66000
10107,20060925,26.95000
10107,20060926,27.20000
10107,20060927,27.44000
10107,20060928,27.40000
10107,20060929,27.35000
10107,20061002,27.36000
10107,20061003,27.37000
10107,20061004,27.94000
10107,20061005,27.92000
10107,20061006,27.87000
10107,20061009,27.72000
10107,20061010,27.69000
10107,20061011,27.54000
10107,20061012,28.22000
10107,20061013,28.37000
10107,20061016,28.45000
10107,20061017,28.44000
10107,20061018,28.52000
10107,20061019,28.29000
10107,20061020,28.43000
10107,20061023,28.45000
10107,20061024,28.28000
10107,20061025,28.31000
10107,20061026,28.35000
10107,20061027,28.34000
10107,20061030,28.53000
10107,20061031,28.71000
10107,20061101,28.81000
10107,20061102,28.77000
10107,20061103,28.73000
10107,20061106,28.84000
10107,20061107,28.95000
10107,20061108,28.98000
10107,20061109,29.26000
10107,20061110,29.24000
10107,20061113,29.35000
10107,20061114,29.22850
10107,20061115,29.12000
10107,20061116,29.47000
10107,20061117,29.40000
10107,20061120,29.89000
10107,20061121,29.92000
10107,20061122,29.92000
10107,20061124,29.76000
10107,20061127,29.48000
10107,20061128,29.39000
10107,20061129,29.57000
10107,20061130,29.36000
10107,20061201,29.12000
10107,20061204,29.33000
10107,20061205,29.13000
10107,20061206,28.99000
10107,20061207,28.85000
10107,20061208,29.40000
10107,20061211,29.54000
10107,20061212,29.43000
10107,20061213,29.55000
10107,20061214,30.07000
10107,20061215,30.19000
10107,20061218,29.89000
10107,20061219,29.99000
10107,20061220,30.09000
10107,20061221,29.98000
10107,20061222,29.64000
10107,20061226,29.99000
10107,20061227,30.02000
10107,20061228,29.98000
10107,20061229,29.86000
;;;;
proc sql;
create table want
as select distinct a.*, b.prc
from have1 as a left join have2 as b
on a.permno=b.permno and intnx('day',a.mend,-30)<=b.date<=intnx('day',a.mend,0); quit;
data have1;
input companyid mend;
informat mend ddmmyy10.;
format mend date9.;
cards;
1 31/12/2017
1 30/11/2017
1 31/10/2017
;
run;
data have2;
input companyid price date_daily;
informat date_daily ddmmyy10.;
format date_daily date9.;
cards;
1 100 31/12/2017
1 200 30/12/2017
1 300 29/11/2017
1 400 20/11/2017
;
run;
/*STEP1: Find the Last Price*/
data have2;
set have2;
mon=month(date_daily);
year=year(date_daily);
run;
proc sort data=have2;
by companyid year mon date_daily;
run;
data have2(drop=year mon);
set have2;
by companyid year mon date_daily;
if last.mon then output;
run;
/*STEP2: Merge with the master set*/
proc sql;
create table want as
select h1.*, h2.price, h2.date_daily
from have1 h1 left join have2 h2 on h1.companyid=h2.companyid
and month(date_daily)=month(mend)
and year(date_daily)=year(mend);
quit;
Please Let us know if it worked for you
Do you really need to merge those files? Please post example input datasets and what should be the result.
@ andreas_lds see sample data
Post some example data if your want a code answer
@PeterClemmensen I posted some sample data.
You did not post the output yet .
data have1;
infile datalines dsd truncover;
input PERMNO:8. MEND:DATE9.;
format PERMNO:8. MEND DATE9.;
month=month(MEND);
datalines4;
10107,30SEP2006
10107,31OCT2006
10107,30NOV2006
10107,31DEC2006
;;;;
data have2;
infile datalines dsd truncover;
input PERMNO:8. DATE:YYMMDD. PRC:12.5;
format PERMNO 8. DATE YYMMDDN8. PRC 12.5;
month=month(date);
datalines4;
10107,20060901,25.84000
10107,20060905,25.61000
10107,20060906,25.61000
10107,20060907,25.43000
10107,20060908,25.60000
10107,20060911,25.91000
10107,20060912,25.93000
10107,20060913,25.98000
10107,20060914,26.33000
10107,20060915,26.85000
10107,20060918,26.79000
10107,20060919,26.86000
10107,20060920,27.18000
10107,20060921,26.90000
10107,20060922,26.66000
10107,20060925,26.95000
10107,20060926,27.20000
10107,20060927,27.44000
10107,20060928,27.40000
10107,20060929,27.35000
10107,20061002,27.36000
10107,20061003,27.37000
10107,20061004,27.94000
10107,20061005,27.92000
10107,20061006,27.87000
10107,20061009,27.72000
10107,20061010,27.69000
10107,20061011,27.54000
10107,20061012,28.22000
10107,20061013,28.37000
10107,20061016,28.45000
10107,20061017,28.44000
10107,20061018,28.52000
10107,20061019,28.29000
10107,20061020,28.43000
10107,20061023,28.45000
10107,20061024,28.28000
10107,20061025,28.31000
10107,20061026,28.35000
10107,20061027,28.34000
10107,20061030,28.53000
10107,20061031,28.71000
10107,20061101,28.81000
10107,20061102,28.77000
10107,20061103,28.73000
10107,20061106,28.84000
10107,20061107,28.95000
10107,20061108,28.98000
10107,20061109,29.26000
10107,20061110,29.24000
10107,20061113,29.35000
10107,20061114,29.22850
10107,20061115,29.12000
10107,20061116,29.47000
10107,20061117,29.40000
10107,20061120,29.89000
10107,20061121,29.92000
10107,20061122,29.92000
10107,20061124,29.76000
10107,20061127,29.48000
10107,20061128,29.39000
10107,20061129,29.57000
10107,20061130,29.36000
10107,20061201,29.12000
10107,20061204,29.33000
10107,20061205,29.13000
10107,20061206,28.99000
10107,20061207,28.85000
10107,20061208,29.40000
10107,20061211,29.54000
10107,20061212,29.43000
10107,20061213,29.55000
10107,20061214,30.07000
10107,20061215,30.19000
10107,20061218,29.89000
10107,20061219,29.99000
10107,20061220,30.09000
10107,20061221,29.98000
10107,20061222,29.64000
10107,20061226,29.99000
10107,20061227,30.02000
10107,20061228,29.98000
10107,20061229,29.86000
;;;;
data want;
set have2 have1(rename=(MEND=date));
by PERMNO month DATE;
retain want_prc;
if first.month then want_prc=.;
if not missing(prc) then want_prc=prc;
if last.month;
drop prc;
run;
Thanks. This is the want table:
permno | mend | prc |
10107 | 30-Sep-06 | 27.35 |
10107 | 31-Oct-06 | 28.71 |
10107 | 30-Nov-06 | 29.36 |
10107 | 31-Dec-06 | 29.86 |
OK. If you have different years data , need add a YEAR variable.
data have1;
infile datalines dsd truncover;
input PERMNO:8. MEND:DATE9.;
format PERMNO:8. MEND DATE9.;
year=year(MEND);
month=month(MEND);
datalines4;
10107,30SEP2006
10107,31OCT2006
10107,30NOV2006
10107,31DEC2006
;;;;
data have2;
infile datalines dsd truncover;
input PERMNO:8. DATE:YYMMDD. PRC:12.5;
format PERMNO 8. DATE YYMMDDN8. PRC 12.5;
year=year(date);
month=month(date);
datalines4;
10107,20060901,25.84000
10107,20060905,25.61000
10107,20060906,25.61000
10107,20060907,25.43000
10107,20060908,25.60000
10107,20060911,25.91000
10107,20060912,25.93000
10107,20060913,25.98000
10107,20060914,26.33000
10107,20060915,26.85000
10107,20060918,26.79000
10107,20060919,26.86000
10107,20060920,27.18000
10107,20060921,26.90000
10107,20060922,26.66000
10107,20060925,26.95000
10107,20060926,27.20000
10107,20060927,27.44000
10107,20060928,27.40000
10107,20060929,27.35000
10107,20061002,27.36000
10107,20061003,27.37000
10107,20061004,27.94000
10107,20061005,27.92000
10107,20061006,27.87000
10107,20061009,27.72000
10107,20061010,27.69000
10107,20061011,27.54000
10107,20061012,28.22000
10107,20061013,28.37000
10107,20061016,28.45000
10107,20061017,28.44000
10107,20061018,28.52000
10107,20061019,28.29000
10107,20061020,28.43000
10107,20061023,28.45000
10107,20061024,28.28000
10107,20061025,28.31000
10107,20061026,28.35000
10107,20061027,28.34000
10107,20061030,28.53000
10107,20061031,28.71000
10107,20061101,28.81000
10107,20061102,28.77000
10107,20061103,28.73000
10107,20061106,28.84000
10107,20061107,28.95000
10107,20061108,28.98000
10107,20061109,29.26000
10107,20061110,29.24000
10107,20061113,29.35000
10107,20061114,29.22850
10107,20061115,29.12000
10107,20061116,29.47000
10107,20061117,29.40000
10107,20061120,29.89000
10107,20061121,29.92000
10107,20061122,29.92000
10107,20061124,29.76000
10107,20061127,29.48000
10107,20061128,29.39000
10107,20061129,29.57000
10107,20061130,29.36000
10107,20061201,29.12000
10107,20061204,29.33000
10107,20061205,29.13000
10107,20061206,28.99000
10107,20061207,28.85000
10107,20061208,29.40000
10107,20061211,29.54000
10107,20061212,29.43000
10107,20061213,29.55000
10107,20061214,30.07000
10107,20061215,30.19000
10107,20061218,29.89000
10107,20061219,29.99000
10107,20061220,30.09000
10107,20061221,29.98000
10107,20061222,29.64000
10107,20061226,29.99000
10107,20061227,30.02000
10107,20061228,29.98000
10107,20061229,29.86000
;;;;
data want;
set have2 have1(rename=(MEND=date));
by PERMNO year month DATE;
retain want_prc;
if first.month then want_prc=.;
if not missing(prc) then want_prc=prc;
if last.month;
drop prc;
run;
@Ksharp thanks. Is there a way to get a more efficient code because my have2 table is over 20GB. It has prices for different stocks.
data have1;
input companyid mend;
informat mend ddmmyy10.;
format mend date9.;
cards;
1 31/12/2017
1 30/11/2017
1 31/10/2017
;
run;
data have2;
input companyid price date_daily;
informat date_daily ddmmyy10.;
format date_daily date9.;
cards;
1 100 31/12/2017
1 200 30/12/2017
1 300 29/11/2017
1 400 20/11/2017
;
run;
/*STEP1: Find the Last Price*/
data have2;
set have2;
mon=month(date_daily);
year=year(date_daily);
run;
proc sort data=have2;
by companyid year mon date_daily;
run;
data have2(drop=year mon);
set have2;
by companyid year mon date_daily;
if last.mon then output;
run;
/*STEP2: Merge with the master set*/
proc sql;
create table want as
select h1.*, h2.price, h2.date_daily
from have1 h1 left join have2 h2 on h1.companyid=h2.companyid
and month(date_daily)=month(mend)
and year(date_daily)=year(mend);
quit;
Please Let us know if it worked for you
@Satish_Parida thanks for the code. Can I use just the last part of the code because my have2 table is over 20GB to make it more efficient.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: