## merge 1 field from 2 rows with same account

Solved
Super Contributor
Posts: 409

# merge 1 field from 2 rows with same account

I appologize for the confusion in the titke..  but what I'm trying to achieve is :  I want to shift one field up if a certain criteria is met for the duplicated accounts..

For example:

BEFORE:

Acct   Field_1   Field_2

001      86            .

001       .             34

002      33            21

003      .               .

Make one row for Acct = 001 if the Field_1 is missing and Field_2 is missing for each dupicated account.

AFTER:

Acct   Field_1   Field_2

001      86            34

002      33            21

003      .               .

Accepted Solutions
Solution
‎03-27-2014 05:03 AM
New Contributor
Posts: 2

## Re: merge 1 field from 2 rows with same account

Hi,

Try this.

data before;

input Acct   Field_1   Field_2;

cards;

001      86            .

001       .            34

002      33            21

003      .             .

;

proc sort data=before out=before_s;

by acct descending field_1 descending field_2;

run;

data after;

set before_s;

retain field1 field2;

by acct descending field_1 descending field_2;

if first.acct then do;

field1=.;

field2=.;

end;

if not missing (field_1) then field1=field_1;

if not missing (field_2) then field2=field_2;

if last.acct;

run;

All Replies
Super Contributor
Posts: 275

## Re: merge 1 field from 2 rows with same account

Is it what you need?

proc sql;

select acct, max(field_1) as field_1, max(field_2) as field_2 from yourfile group by acct;

quit;

Frequent Contributor
Posts: 106

## Re: merge 1 field from 2 rows with same account

hi try this,

data test;

input Acct \$3. Field_1   Field_2  ;

cards;

001 86 .

001 . 34

002 33 21

003 . .

;

data test;

update test(obs=0) test;

by acct;

run;

Solution
‎03-27-2014 05:03 AM
New Contributor
Posts: 2

## Re: merge 1 field from 2 rows with same account

Hi,

Try this.

data before;

input Acct   Field_1   Field_2;

cards;

001      86            .

001       .            34

002      33            21

003      .             .

;

proc sort data=before out=before_s;

by acct descending field_1 descending field_2;

run;

data after;

set before_s;

retain field1 field2;

by acct descending field_1 descending field_2;

if first.acct then do;

field1=.;

field2=.;

end;

if not missing (field_1) then field1=field_1;

if not missing (field_2) then field2=field_2;

if last.acct;

run;

Super Contributor
Posts: 409

## Re: merge 1 field from 2 rows with same account

Thanks vasu.. this is exactly what I was looking for.

🔒 This topic is solved and locked.