topic Re: Inserting multiple blank rows into a table or dataset on a condition in SAS Programming

Inserting multiple blank rows into a table or dataset on a condition

Akarsh91 — Sun, 28 May 2017 19:27:45 GMT

Dear People,

I would like to know how can I insert multiple blank rows into a sas table based on a condition. I have the following:

I want to do the following:

If the month is equal to 1 then I want to insert 11 blank rows after the row where month is equal to 1. If the month is 8 for example then I want to insert 4 blank rows after the row where month is equal to 4 and so on such that the total doesn't exceed 12 months. I am not able to figure out what code to use in order to accomplish this task. Could anyone please help me by providing a code that could accomplish the above task? If you need more information, please ask. Would appreciate your help. I am using sas version 9.4

Regards

Akarsh

Re: Inserting multiple blank rows into a table or dataset on a condition

Shmuel — Sun, 28 May 2017 20:06:33 GMT

You can create a nwe dataset by:

data want;
 set have;
       array all_n _numeric_;
       output;
       if month < 12 then
         do i=month +1 to 12; 
              call missing(all_n:);
              month = i;  /* all missing except month ? */
              output; 
        end;
run;

Re: Inserting multiple blank rows into a table or dataset on a condition

Reeza — Sun, 28 May 2017 20:41:36 GMT

What's the logic here? Are you trying to create a continous dataset with all months for all years?

If so, search for PRELOADFMT on here and you'll find the best solutions on how to include the missing data you want.

FYI In the future please post your data as text, especially if you want code. I'm not typing out your data.

@Akarsh91 wrote:

Dear People,

I would like to know how can I insert multiple blank rows into a sas table based on a condition. I have the following:

I want to do the following:

If the month is equal to 1 then I want to insert 11 blank rows after the row where month is equal to 1. If the month is 8 for example then I want to insert 4 blank rows after the row where month is equal to 4 and so on such that the total doesn't exceed 12 months. I am not able to figure out what code to use in order to accomplish this task. Could anyone please help me by providing a code that could accomplish the above task? If you need more information, please ask. Would appreciate your help. I am using sas version 9.4

Regards

Akarsh

Re: Inserting multiple blank rows into a table or dataset on a condition

mkeintz — Sun, 28 May 2017 21:07:34 GMT

@Reeza

What the OP has is apparently a list of PERMNO's (stock id's) with data for the last month the given stock is publically listed on a major american stock exchange. The "Names Date" is the delisting date.

I suppose that @Akarsh91 wants to create complete calendar year history for the delisted stocks - possibly to have all stocks with the same set of records, even if some records are dummy records. Presumarby he/she already has monthly stock data preceding the delisting events.

Re: Inserting multiple blank rows into a table or dataset on a condition

Akarsh91 — Sun, 28 May 2017 21:26:20 GMT

@mkeintz

Yes. I have Permno's and the stocks are delisted on the date that is mentioned. My main goal however is to replace the returns of with corresponding size decile returns (which I have) until the end of the year. For instance, the average size decile return is 0.02. The stock gets delisted on february 1984. From march 1984 to december 1984 the stock will be assigned the return of 0.02. The stock won't be taken into consideration after 1984. To accomplish this, i thought to put empty rows as mentioned above in my original post and then replace the returns with the corresponding size decile returns. If you think there are more better or optimal ways to accomplish this task, please help.

Thanks in advance

Regards

Akarsh

Re: Inserting multiple blank rows into a table or dataset on a condition

Reeza — Sun, 28 May 2017 21:32:34 GMT

Create the dataset with the dates and values you want and insert those (via proc append) and the sort to get your desired order.