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

Reply
Occasional Contributor
Posts: 17

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

[ Edited ]

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: 

 

Capture1.PNG

 

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 

Trusted Advisor
Posts: 1,466

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

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;
Super User
Posts: 19,105

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

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: 

 

Capture1.PNG

 

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 


 

Valued Guide
Posts: 947

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

@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.

Occasional Contributor
Posts: 17

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

[ Edited ]

@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 

Super User
Posts: 19,105

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

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

Ask a Question
Discussion stats
  • 5 replies
  • 221 views
  • 0 likes
  • 4 in conversation