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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.