BookmarkSubscribeRSS Feed
mrho
Calcite | Level 5

I have a data set that follows a specific portfolio monthly from 05-2004 to 05-2008. What I am trying to do is track which stocks remain in the portfolio, which ones are removed, and which ones replace the ones that were removed. Basically, trying to count the number of changes to see if the financial crises approached (2008) if the portfolio manager started making more changes to the portfolio.

I'm not exactly sure how to tackle this problem. Frequency crossed my mind and maybe separating my data in to monthly data sets and then trying to merge them based on stock name.

Stock names are a character value in the $14 format, and the date is in YYMMDD10. format.

Any advice or thoughts would be greatly appreciated.

Thanks!

3 REPLIES 3
Kurt_Bremser
Super User

I'd start by sorting the dataset by portfolio and date.

Then go through it like

data want;

set have;

by portfolio;

retain last_month start end;

if first.portfolio then do;

  start = date;

  last_month = .;

end;

if last.portfolio then do;

  end = date;

  output;

end;

else do;

  if date ne last_month + 1 then do; * gap detected;

  end = last_month;

  output;

  start = date;

end;

last_month = date;

run;

Now you have a dataset where you can correlate when there were months with lots of start and/or end dates, and with some more work you can find the records where end in one portfolio coincides with start in another.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Should be pretty straight forward, you merge the two datasets based on a set of id variables, and have a conditional to show if removed etc.

data have;

  stock_name="ABCD"; date="01MAY2004"d; output;

  stock_name="ABCD"; date="01JUN2004"d; output;

run;

data new;

  stock_name="ABCD"; date="01JUN2004"d; output;

  stock_name="BCDE"; date="01MAY2004"d; output;

run;

proc sql;

  create table REPORT as

  select  COALESCE(A.STOCK_NAME,B.STOCK_NAME) as STOCK_NAME,

          A.DATE as CURRENT_DATE format=date9.,

          B.DATE as NEW_DATE format=date9.,

          case  when A.STOCK_NAME is null then "New"

                when B.STOCK_NAME is null then "Removed"

                when A.DATE ne B.DATE then "Date Changed"

                else "" end as RESULT

  from    HAVE A

  full join NEW B

  on      A.STOCK_NAME=B.STOCK_NAME;

quit;

Note, you can do the same in datastep, just means you need to make sure both datasets are sorted first.

Ksharp
Super User

Post your data and the output you need.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 3 replies
  • 1084 views
  • 7 likes
  • 4 in conversation