Updating one field in a SAS data set

[GreggB]

My objective is twofold:  1) find new enrollees in our SIS database, 2) find students who are not new enrollees but have transferred to another school, as evidenced by a change in School ID. New enrollees and transfers will be exported to a csv file for upload to an ftp site.  Additionally, because I repeat this process over a 6-week window, new enrollees need to be added to the data set I call "precode" so that I won't flag them as new.  I have no trouble at this point.  Here's the rub: Transfer students already exist in "precode" but their School ID must be updated. I'm having trouble with this.

Precode:   student     schoolid      stateid      

                   tom             005          1

                   bill               004          2

                   fred             005          3

SIS :   student                BEDS      student_number      

                   tom             005          1

                   bill               004          2

                   fred             008          3

                   sam             016          4

NOTE:   fred is a transfer and his schoolid needs updating in Precode. sam is new and needs to  be added to Precode. 

proc sql;
create table join1 as select
s.lastfirst as student, s.student_Number, s.beds,
p.* 
from SIS as s
left join precode as p
on s.studentnumber=p.stateid;
quit;

*** NEW STUDENTS *** ;
data new_stud;
set join1;
where schoolid=. and stateid=.;
run;
*** TRANSFERS *** ;
data transfers;
set join1;
where schoolid ne . and stateid ne . and schoolid ne beds;
run;

[ChrisNZ]

One way:

data PRECODE;
  update PRECODE(in=A)
         SIS(rename=(STUDENT_NUMBER=STATEID) in=B);
  by STATEID;
  NEW = (B & ^A);
  XFER= (SCHOOLID ne BEDS & ^NEW);
run; 

STUDENT	SCHOOLID	STATEID	BEDS	NEW	XFER
tom	5	1	5	0	0
bill	4	2	4	0	0
fred	5	3	8	0	1
sam	.	4	16	1	0

[ShiroAmada]

Try this.

data precode;
 infile datalines dlm="|";
 input 
	student: $10.
	schoolid: $3.
	stateid: 8.;
datalines;
tom|005|1
bill|004|2
fred|005|3
;
run;

data SIS;
 infile datalines dlm="|";
 input 
	student: $10.
	BEDS: $3.
	student_number: 8.;
datalines;
tom|005|1
bill|004|2

fred|008|3
sam|016|4
;
run;

proc sql;
  create table all as
  select
  coalescec(b.student,a.student) as student,
  coalescec(b.beds,a.schoolid) as schoolid,
  coalesce(b.student_number,a.stateid) as stateid
  from precode a full join sis b
  on a.stateid=b.student_number;
quit;