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 . .
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;
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;
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;
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;
Thanks vasu.. this is exactly what I was looking for.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.