DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 76
Accepted Solution

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

[ Edited ]

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;

 


Accepted Solutions
Solution
‎02-14-2018 10:38 PM
Frequent Contributor
Posts: 112

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

[ Edited ]
Posted in reply to Agent1592
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

View solution in original post


All Replies
Valued Guide
Posts: 624

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

Posted in reply to Agent1592

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

 

Frequent Contributor
Posts: 76

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

Posted in reply to andreas_lds

@ andreas_lds see sample data

PROC Star
Posts: 1,400

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

Posted in reply to Agent1592

Post some example data if your want a code answer

Frequent Contributor
Posts: 76

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

@draycut I posted some sample data.

Super User
Posts: 10,846

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

Posted in reply to Agent1592

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;
Frequent Contributor
Posts: 76

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

 

@Ksharp

Thanks. This is the want table:

permnomendprc
1010730-Sep-0627.35
1010731-Oct-0628.71
1010730-Nov-0629.36
1010731-Dec-0629.86
Super User
Posts: 10,846

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

Posted in reply to Agent1592

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;
Frequent Contributor
Posts: 76

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

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

Solution
‎02-14-2018 10:38 PM
Frequent Contributor
Posts: 112

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

[ Edited ]
Posted in reply to Agent1592
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

Frequent Contributor
Posts: 76

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

Posted in reply to Satish_Parida

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 178 views
  • 1 like
  • 5 in conversation