BookmarkSubscribeRSS Feed
Akarsh91
Calcite | Level 5

Dear Experts, 

 

I have the following information: 

 

ID    Date                Returns      Delisting return        month         year

1   1967-10-29     0.025                                            10            1967

1  1967-11-29      0.026                                            11            1967

1    1967-12-29     0.027                                            12            1967

1    1968-01-29                                   0.01                  1             1968

 

The stock (ID=1) gets delisted on 1968-01-29. From 1968-02-01 onwards i want to assign a return of 0.025 until the end of the year that is 1968-12-29. i am thinking of creating missing values and then replacing those with the return, so the output I would like to obtain would be as follows:

 

ID Date                Returns      Delisting return        month         year

1   1967-10-29     0.025                                            10            1967

1   1967-11-29      0.026                                            11            1967

1    1967-12-29     0.027                                            12            1967

1    1968-01-29      0.01                     0.01                  1             1968

1    1968-02-29          .                                                2              1968

1    1968-03-29          .                                                3              1968

1     1968 -04-29        .                                                4              1968

 

And so on until the end of the year. I want the month and the year column filled up. 

 

I can then later replace the missing values with the return I want to assign. Could anyone please suggest a simplest way to accomplish this or provide a code? 

 

Looking forward to your reply. Please write if you need anymore information. I am using SAS 9.4

 

Regards 

Akarsh 

3 REPLIES 3
Patrick
Opal | Level 21

@Akarsh91

Please provide in the future a SAS datastep creating the sample data. You're asking for other peoples expertise and time so I'd consider you doing this work instead us as an act of courtesy.

 

You're aligning all your dates to the 29th. Not sure what should happen with non-leap year Februaries. The code below will return the 28th for such cases.

data have;
  infile datalines dlm=',' dsd truncover;
  input ID Date:anydtdte. Returns Delisting_return month year;
  format date date9.;
  datalines;
1,1967-10-29,0.025,,10,1967
1,1967-11-29,0.026,,11,1967
1,1967-12-29,0.027,,12,1967
1,1968-01-29,,0.01,1,1968
;
run;

data want;
  set have;
  by id;
  output;

  if last.id and month(date) ne 12 then
    do;
      call missing(Returns,Delisting_return);
      do while(month(date) ne 12);
        date=min(intnx('month',date,1,'b')+28 ,intnx('month',date,1,'e') );
        month=month(date);
        year=year(date);
        output;
      end;
    end;
run;
Akarsh91
Calcite | Level 5

@ Partick

Thank you patrick. The code worked and gave the desired output. I apologize for not providing the SAS datastep. However, the data I am using is downloaded from CRSP, so I didn't creat any datastep on my own. Anyways, thanks a lot.

ballardw
Super User

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

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