BookmarkSubscribeRSS Feed
Akarsh91
Calcite | Level 5

Dear experts, 

 

I have the following data set:

 

data have;
  infile datalines dlm=',' dsd truncover;
  input ID Date:anydtdte. Returns Delisting_return month newmonth year;
  format date date9.;
  datalines;
1,1967-10-28,1.025,,10,07,1967
1,1967-11-28,1.026,,11,08,1967
1,1967-12-28,1.027,,12,09,1967
1,1968-01-28,1.01,,1,10,1968
1,1968-02-28,1.04,,2,11,1968
1,1968-03-28,1.001,,3,12,1968
1,1968-04-28,1.005,,4,01,1968
1,1968-05-28,1.02,,5,02,1968
1,1968-06-28,1.02,,6,03,1968
1,1968-07-28,1.06,,7,04,1968
1,1968-08-28,1.06,,8,05,1968
1,1968-09-28,1.0014,1.0014,9,06,1968;
run; 


 

 

ID    Date                Returns      Delisting return        month   newmonth      year

1   1967-10-28     1.025                                            10              07                1967

1  1967-11-28      1.026                                            11               08               1967

1   1967-12-28     1.027                                            12               09                1967

1    1968-01-28      1.01                                             1               10                1968

1    1968-02-28    1.04                                              2                11                  1968

1    1968-03-28     1.001                                           3                12                 1968

1   1968 -04-28     1.005   .                                       4                1                  1968

1   1968-05-28     1.02                                              5                 2                 1968

1   1968-06-28      1.02                                             6                 3              1968

1    1968-07-28     1.06                                              7                4               1968

1    1968-08-28      1.06                                             8                 5           1968

1   1968-09-28       1.0014                    1.0014          9                 6            1968

 

 

Id is only 1 here because I only have 1 stock in a portfolio. I am replicating a paper. I buy a stock in april (04 1968). so this becomes the first month for me (the month I bought the stock in(newmonth in the table above). For the purpose of the paper, if the stock gets delisted then the return is  replaced with the size decile return until the end of the year, relative to formation. So i bought the stock in april 1968 i only held it for 6 months. So for me it got delisted after 6 months I bought it. So for the months 7 - 12 (actual 9-1968, to 4-1969) the return is to be replaced with the size delcille return. So the output I would like to obtain is as follows: 

 

ID    Date                Returns      Delisting return        month   newmonth      year

1   1967-10-28     1.025                                            10              07                1967

1  1967-11-28      1.026                                            11               08               1967

1   1967-12-28     1.027                                            12               09                1967

1    1968-01-28      1.01                                             1               10                1968

1    1968-02-28    1.04                                              2                11                  1968

1    1968-03-28     1.001                                           3                12                 1968

1   1968 -04-28     1.005   .                                       4                1                  1968

1   1968-05-28     1.02                                              5                 2                 1968

1   1968-06-28      1.02                                             6                 3              1968

1    1968-07-28     1.06                                              7                4               1968

1    1968-08-28      1.06                                             8                 5           1968

1   1968-09-28       1.0014                    1.0014          9                 6            1968

1   1968-10-28           .                                .            10                 7            1968

1    1968-11-28          .                               .             11                  8          1968

1    1968-12-28         .                                              12                 9           1968

1    1969-01-28         .                                .             1                  10           1968

1    1969-02-28         .                                .             2                  11           1968 

1    1969-03-28         .                                .             3                  12           1968

1    1969-04-28         .                                .             4                  01          1968

 

I would like to create empty rows according to the new month. If the stock gets delisted in the new month = 06 then i want to insert empty rows after that with missing values for return and delisting return as above until the new month = 12. 

 

I have tried using this code code

 

data want; 
set have;
by ID;
output;
if last.ID and newmonth < 12 then do;
call missing(ret, dlret);
do while (newmonth <12);
output;
end
end;
run;

Sas however stops working whenever I used it. Is there any other code that I could use to accomplish the above task? Or could anyone correct this code?

 

All help is appreciated. Thankyou in advance. 

 

Regards

Akarsh91

 

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Something like:

data have;
  infile datalines dlm=',' dsd truncover;
  input ID Date:anydtdte. Returns Delisting_return month newmonth year;
  format date date9.;
  datalines;
1,1967-10-28,1.025,,10,07,1967
1,1967-11-28,1.026,,11,08,1967
1,1967-12-28,1.027,,12,09,1967
1,1968-01-28,1.01,,1,10,1968
1,1968-02-28,1.04,,2,11,1968
1,1968-03-28,1.001,,3,12,1968
1,1968-04-28,1.005,,4,01,1968
1,1968-05-28,1.02,,5,02,1968
1,1968-06-28,1.02,,6,03,1968
1,1968-07-28,1.06,,7,04,1968
1,1968-08-28,1.06,,8,05,1968
1,1968-09-28,1.0014,1.0014,9,06,1968
;
run; 

data want (drop=i);
  set have;
  by id;
  if last.id and month(date) ne 12 then do;
    do i=0 to 11-month(date);
      date=intnx('month',date,i);
      if i > 0 then returns=.;
      month=month(date);
      newmonth=.;
      year=year(date);
      output;
    end;
  end;
  else output;
run;
Akarsh91
Calcite | Level 5

@RW9 

 

Thank you for your reply. I would like to however have the output I mentioned above: 

 

  1968 -04-28     1.005   .                                       4                1                  1968

1   1968-05-28     1.02                                              5                 2                 1968

1   1968-06-28      1.02                                             6                 3              1968

1    1968-07-28     1.06                                              7                4               1968

1    1968-08-28      1.06                                             8                 5           1968

1   1968-09-28       1.0014                    1.0014          9                 6            1968

1   1968-10-28           .                                .            10                 7            1968

1    1968-11-28          .                               .             11                  8          1968

1    1968-12-28         .                                              12                 9           1968

1    1969-01-28         .                                .             1                  10           1968

1    1969-02-28         .                                .             2                  11           1968 

1    1969-03-28         .                                .             3                  12           1968

 

My stock gets delisted on the actual date of 9-1968. I need to replace the return with an average value ( in this case i want it to be missing, hence the rows with missing values), until the march next  next year. For example if stock gets delisted on 09 1968 then i want rows with missing values until 03 1969. Since I am buying stocks on april it will be month 1 for me after i bought the stock. So I created the column new month. 

 

With the code you sent I only get the missing values until the month of 12 (actual month). I want to obtain emplty rows according to the new month. If new month is =6 then I would like to obtain 6 missing rows underneath. Is it possible to adjust the code you sent such that the missing values are created for the formation of april to april acccording to the new monthas above? Could you please help with adjusting it? 

 

Akarsh 91. 

 

PS: I am using sas 9.4

ballardw
Super User

Perhaps you intend

1    1969-01-28         .                                .             1                  10           1969

1    1969-02-28         .                                .             2                  11           1969 

1    1969-03-28         .                                .             3                  12           1969

 

For the last 3 lines?

 

And this rule " 09 1968 then i want rows with missing values until 03 1969" does NOT tell what to do if another stock is delisted 06 1969.

Why the magic end date ot 03 1969? What is determining that is the last date? Your original input example data does not show anything that would trigger 1968-04 to be special for anything in theis process. Your narrative mentions "Since I am buying stocks on april it will be month 1 for me after i bought the stock" but there is nothing in the start data to indicate that.

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Updated:

data have;
  infile datalines dlm=',' dsd truncover;
  input ID Date:anydtdte. Returns Delisting_return month newmonth year;
  format date date9.;
  datalines;
1,1967-10-28,1.025,,10,07,1967
1,1967-11-28,1.026,,11,08,1967
1,1967-12-28,1.027,,12,09,1967
1,1968-01-28,1.01,,1,10,1968
1,1968-02-28,1.04,,2,11,1968
1,1968-03-28,1.001,,3,12,1968
1,1968-04-28,1.005,,4,01,1968
1,1968-05-28,1.02,,5,02,1968
1,1968-06-28,1.02,,6,03,1968
1,1968-07-28,1.06,,7,04,1968
1,1968-08-28,1.06,,8,05,1968
1,1968-09-28,1.0014,1.0014,9,06,1968
;
run; 

data want (drop=i fst_date fst_newmonth);
  set have;
  by id;
  retain fst_date fst_newmonth;
  if last.id and month(date) ne 12 then do;
    fst_date=date;
    fst_newmonth=newmonth;
    do i=0 to 12-newmonth;
      date=intnx('month',fst_date,i);
      if i > 0 then returns=.;
      month=month(date);
      year=year(date);
      delisting_return=.;
      newmonth=fst_newmonth+i;
      output;
    end;
  end;
  else output;
run;

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