BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Agent1592
Pyrite | Level 9

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Satish_Parida
Lapis Lazuli | Level 10
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

10 REPLIES 10
andreas_lds
Jade | Level 19

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

 

Agent1592
Pyrite | Level 9

@ andreas_lds see sample data

PeterClemmensen
Tourmaline | Level 20

Post some example data if your want a code answer

Ksharp
Super User

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;
Agent1592
Pyrite | Level 9

 

@Ksharp

Thanks. This is the want table:

permnomendprc
1010730-Sep-0627.35
1010731-Oct-0628.71
1010730-Nov-0629.36
1010731-Dec-0629.86
Ksharp
Super User

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;
Agent1592
Pyrite | Level 9

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

Satish_Parida
Lapis Lazuli | Level 10
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

Agent1592
Pyrite | Level 9

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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