topic Re: merge 1 field from 2 rows with same account in SAS Procedures

merge 1 field from 2 rows with same account

podarum — Wed, 26 Mar 2014 19:51:38 GMT

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

Re: merge 1 field from 2 rows with same account

slchen — Wed, 26 Mar 2014 20:15:21 GMT

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;

Re: merge 1 field from 2 rows with same account

pradeepalankar — Wed, 26 Mar 2014 20:43:43 GMT

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;

Re: merge 1 field from 2 rows with same account

vasu — Thu, 27 Mar 2014 09:03:12 GMT

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;

Re: merge 1 field from 2 rows with same account

podarum — Mon, 31 Mar 2014 20:05:15 GMT

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