Help using Base SAS procedures

merge 1 field from 2 rows with same account

Accepted Solution Solved
Reply
Super Contributor
Posts: 401
Accepted Solution

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;

View solution in original post


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: 401

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.

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

Discussion stats
  • 4 replies
  • 307 views
  • 1 like
  • 4 in conversation