Tracking Changes from month to month

[mrho]

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!

[Kurt_Bremser]

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]

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]

Post your data and the output you need.