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

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

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

Hi,

I have data in this format....

Header 1Header 2Header 3Header 4
1AARAP12345
2AAD
3AAC

4

AARAP34521
5AAF
6AAD

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 1Header 2Header 3Header 4
1AARAP12345RAP12345
2AADRAP12345
3AACRAP12345

4

AARAP34521RAP34521
5AAFRAP34521
6AADRAP34521

Accepted Solutions
Solution
‎11-13-2014 04:31 PM
Trusted Advisor
Posts: 1,203

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

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;

View solution in original post


All Replies
Solution
‎11-13-2014 04:31 PM
Trusted Advisor
Posts: 1,203

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

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;

Grand Advisor
Posts: 10,239

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

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

Respected Advisor
Posts: 3,124

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

"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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 184 views
  • 3 likes
  • 4 in conversation