I would appreciate any help with the following problem
I have two data sets:
Data set (1) contains transactions by different a sample of firms I am interested in
e.g.
Firmid data transaction price amount
42324df 1996/07/15 10.9 6000
42324df 1999/06/10 13.5 55000
42324df 2003/10/15 20.7 33000
23492fg 1997/06/10 6.9 10000
23492fg 1999/01/20 16.9 55000
23492fg 2000/07/10 2.50 210000
23492fg 2003/06/10 4.50 110000
Data set (2) contains the historical pricing data for the entire universe of firms on a daily basis for a long period of time.
e.g.
Firmid data transaction price
42324df 1991/01/01 5.00
... .... ...
... ... ... the ... represent further daily pricing data for the respective stocks
... ... ....
... .... ...
... ... ...
23492fg 1997/06/10 13.50
... .... ...
... ... ...
... ... ....
... .... ...
... ... ...
12308hda 1995/02/10 150.00
... .... ...
... ... ...
... ... ....
... .... ...
... ... ...
I am trying to do the following:
Any help is greatly appreciated
For 1.
I would first set a consecutive no pricing days on each price date record in B. This could be fairly simple by sorting it by firmid and date, and then just augment a counter. Any other business rules need to taken care, such as if there is period with no pricing etc. If your data is large you could optimize by just output the records where your "pricing day counter" is 200 or more.
As step two, join A with the result above (and filter out where pricing days less than 200, if not done in the previous step).
Hi,
Here is my try...Hope it meets the requirement...
/*In Two Step*/
proc sql;
create table reg_data(drop = temp) as
select a.*,year(a.data) as temp,count(calculated temp) as tot_days
from pop as a,pop as b
where a.data= b.data
group by a.year
having tot_days GE 200
order by a.data;
quit;
proc sql;
create table reg_data as
select a.*
from reg_data as a right join sample as b
on a.firmid = b.firmid;
quit;
/*In one step*/
proc sql;
create table reg_data(drop = temp) as
select c.*,year(a.data) as temp,count(calculated temp) as tot_days
from pop as a left join
pop as b
on a.data = b.data right join
sample as c
on a.firmid = c.firmid
group by a.year
having tot_days GE 200
order by a.data;
quit;
-Urvish
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 25. 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.