topic Re: Merge two files Last trading price at the end of each month in SAS Programming

Merge two files Last trading price at the end of each month

Agent1592 — Tue, 13 Feb 2018 08:43:13 GMT

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;

Re: Merge two files Last trading price at the end of each month

andreas_lds — Tue, 13 Feb 2018 08:04:25 GMT

Do you really need to merge those files? Please post example input datasets and what should be the result.

Re: Merge two files Last trading price at the end of each month

PeterClemmensen — Tue, 13 Feb 2018 08:07:17 GMT

Post some example data if your want a code answer

Re: Merge two files Last trading price at the end of each month

Agent1592 — Tue, 13 Feb 2018 08:44:22 GMT

@PeterClemmensen I posted some sample data.

Re: Merge two files Last trading price at the end of each month

Agent1592 — Tue, 13 Feb 2018 08:44:51 GMT

@ andreas_lds see sample data

Re: Merge two files Last trading price at the end of each month

Ksharp — Tue, 13 Feb 2018 08:58:00 GMT

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;

Re: Merge two files Last trading price at the end of each month

Agent1592 — Tue, 13 Feb 2018 09:13:44 GMT

@Ksharp

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

Re: Merge two files Last trading price at the end of each month

Ksharp — Tue, 13 Feb 2018 09:20:59 GMT

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;

Re: Merge two files Last trading price at the end of each month

Satish_Parida — Tue, 13 Feb 2018 10:17:50 GMT

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

Re: Merge two files Last trading price at the end of each month

Agent1592 — Tue, 13 Feb 2018 18:57:34 GMT

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

Re: Merge two files Last trading price at the end of each month

Agent1592 — Tue, 13 Feb 2018 19:16:53 GMT

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