topic Updating one field in a SAS data set in SAS Programming

Updating one field in a SAS data set

GreggB — Wed, 08 May 2019 19:21:44 GMT

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;

Re: Updating one field in a SAS data set

ChrisNZ — Thu, 09 May 2019 00:03:17 GMT

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

Re: Updating one field in a SAS data set

ShiroAmada — Thu, 09 May 2019 09:43:52 GMT

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;