How to create a column with first row until the next one occurs(then fill with the new one)

[rkolupoti9001]

Hi,

I have data in this format....

Header 1	Header 2	Header 3	Header 4
1	AA	RAP12345
2	AA	D
3	AA	C
4	AA	RAP34521
5	AA	F
6	AA	D

How to create this to this format.(there are more than 300 occurances in a single file or these with more than 1 million rows.)

The header 4 should be filled with data when ever it starts with RAP...

Header 1	Header 2	Header 3	Header 4
1	AA	RAP12345	RAP12345
2	AA	D	RAP12345
3	AA	C	RAP12345
4	AA	RAP34521	RAP34521
5	AA	F	RAP34521
6	AA	D	RAP34521

[stat_sas · Accepted solution]

data have;

input Header1 Header2 $ Header3 $;

datalines;

1 AA RAP12345

2 AA D

3 AA C

4 AA RAP34521

5 AA F

6 AA D

;

data want;

set have;

retain Header4;

if find(Header3,'RAP') then Header4=Header3;

run;

[ballardw]

length header4 $ 8 ; /* or whatever length header3 has before the Retain*/

[Haikuo]

"when ever it starts with RAP" makes me thinking, instead of " if find(Header3,'RAP') then Header4=Header3; ", using:

if header3 =:'RAP'  then Header4=Header3;

Regards,

Haikuo