topic Re: Updating column in merged dataset based on comparison with old dataset in SAS Programming

Updating column in merged dataset based on comparison with old dataset

OLUGBOJI — Thu, 14 Feb 2019 20:13:22 GMT

I have a dataset that is submitted each month. It consists of a list of teachers and the classes they teach. If a teacher shows up in one class list for this month and they don't show up in the class for next month, the endDate of the column for the newly merged dataset will be updated. How do I update the endDate column of the merged dataset. In my example , the teacher with id 1 did not show up for class B in februrary so the end date is January.

Dataset for January

id className startdate enddate
1 A 01/01/2019
1 B 01/01/2019
2 A 01/01/2019
3 B 01/01/2019

Dataset for February
id className startdate enddate
1 A 01/02/2019
2 A 01/02/2019
3 B 01/02/2019

Dataset for march
id schoolName startdate enddate
1 A 01/03/2019

1 B 01/03/2019
3 B 01/03/2019

Data Want

id className startdate enddate

1 A 01/01/2019
1 B 01/01/2019 01/01/2019
1 A 01/02/2019
1 A 01/03/2019

1 B 01/03/2019
2 A 01/01/2019
2 A 01/02/2019 01/02/2019
3 B 01/01/2019
3 B 01/02/2019
3 B 01/03/2019

Re: Updating column in merged dataset based on comparison with old dataset

tomrvincent — Thu, 14 Feb 2019 17:35:06 GMT

left join jan to feb by id and class. Then enddate in 'want' would be something like 'case when feb.endate is missing then jan.startdate end'

Re: Updating column in merged dataset based on comparison with old dataset

ErikLund_Jensen — Thu, 14 Feb 2019 18:01:48 GMT

Hi OLUGBOJI

One way:


data january;
informat id 1. class $1. startdate ddmmyy10.;
format startdate ddmmyyd10.;
input id class startdate;
datalines;
1   A               01/01/2019
1   B               01/01/2019
2   A               01/01/2019
3   B               01/01/2019
;
run;

data february;
informat id 1. class $1. startdate ddmmyy10.;
format startdate ddmmyyd10.;
input id class startdate;
datalines;
1       A           01/02/2019
2       A           01/02/2019
3       B           01/02/2019
;
run;
 
data all; set january february;
run;

* max enddate pr. class;
proc sql;
create table classmax as
	select distinct class, max(startdate) as classenddate format=ddmmyyd10.
	from all
	group by class;
quit;

* max enddate pr. teacher/class;
proc sql;
create table teacherclassmax as
	select distinct id, class, max(startdate) as tcenddate format=ddmmyyd10.
	from all
	group by id, class;
quit;
 
* join max. enddate pr. class on all classes;
proc sql;
	create table all_classend as
		select all.id, all.class, all.startdate, classmax.classenddate
        from all left join classmax
		on all.class = classmax.class;
quit;

* join teacher/class-enddate, if not = class-enddate;
proc sql;
	create table want as
		select all_classend.id, all_classend.class, all_classend.startdate, teacherclassmax.tcenddate
        from all_classend left join teacherclassmax
		on 
			all_classend.id = teacherclassmax.id and
			all_classend.class = teacherclassmax.class and
			all_classend.classenddate ne teacherclassmax.tcenddate
		order by id, class, startdate;
quit;

result:

Re: Updating column in merged dataset based on comparison with old dataset

novinosrin — Thu, 14 Feb 2019 18:03:02 GMT

data jan;
infile cards truncover;
input id className $ startdate  :ddmmyy10.       enddate :ddmmyy10. ;
format startdate  ddmmyy10.       enddate ddmmyy10. ;
cards;
1   A               01/01/2019
1   B               01/01/2019
2   A               01/01/2019
3   B               01/01/2019
;

data feb;
infile cards truncover;
input id    className $  startdate  :ddmmyy10.     enddate  :ddmmyy10. ;
format startdate  ddmmyy10.       enddate ddmmyy10. ;
cards;
1       A               01/02/2019
2       A                01/02/2019
3       B                01/02/2019
;

data want ;
if _n_=1 then do;
   dcl hash H (dataset:'feb') ;
   h.definekey  ("id","classname") ;
   h.definedata ("startdate", "enddate") ;
   h.definedone () ;
end;
set jan;
if h.check()= 0 then do;
output;
h.find();
output;
end;
else do;
end_date=startdate;
output;
end;
format end_Date ddmmyy10.;
run;

Re: Updating column in merged dataset based on comparison with old dataset

Astounding — Thu, 14 Feb 2019 18:05:03 GMT

In your sample data, it looks like both data sets are in sorted order by ID ClassName StartDate. If that's the case:

data want;
set january (in=from_jan) february;
by id className startdate;
if last.className and from_jan then enddate = startdate;
run;

Re: Updating column in merged dataset based on comparison with old dataset

novinosrin — Thu, 14 Feb 2019 18:19:18 GMT


data jan;
infile cards truncover;
input id className $ startdate  :ddmmyy10.       enddate :ddmmyy10. ;
format startdate  ddmmyy10.       enddate ddmmyy10. ;
cards;
1   A               01/01/2019
1   B               01/01/2019
2   A               01/01/2019
3   B               01/01/2019
;

data feb;
infile cards truncover;
input id    className $  startdate  :ddmmyy10.     enddate  :ddmmyy10. ;
format startdate  ddmmyy10.       enddate ddmmyy10. ;
cards;
1       A               01/02/2019
2       A                01/02/2019
3       B                01/02/2019
;

proc sql;
create table want as
select id,classname, startdate,ifn(count(*)=1,startdate,.) as end_date format ddmmyy10.
from
(select * from jan
union 
select * from feb)
group by id,classname
order by id,classname, startdate;
quit;

Re: Updating column in merged dataset based on comparison with old dataset

novinosrin — Thu, 14 Feb 2019 18:23:27 GMT


data jan;
infile cards truncover;
input id className $ startdate  :ddmmyy10.       enddate :ddmmyy10. ;
format startdate  ddmmyy10.       enddate ddmmyy10. ;
cards;
1   A               01/01/2019
1   B               01/01/2019
2   A               01/01/2019
3   B               01/01/2019
;

data feb;
infile cards truncover;
input id    className $  startdate  :ddmmyy10.     enddate  :ddmmyy10. ;
format startdate  ddmmyy10.       enddate ddmmyy10. ;
cards;
1       A               01/02/2019
2       A                01/02/2019
3       B                01/02/2019
;
data want;
set jan feb;
by id classname;
if first.classname and last.classname then endDate=startdate;
run;

Re: Updating column in merged dataset based on comparison with old dataset

OLUGBOJI — Thu, 14 Feb 2019 18:29:05 GMT

Thanks. This appears to be more efficient

Re: Updating column in merged dataset based on comparison with old dataset

novinosrin — Thu, 14 Feb 2019 18:32:07 GMT

No, I am afraid I made a mistake in not thinking forward from future month appends taking into account B could occur twice . You chose the right answer.

@ErikLund_Jensen 's solution covers a grand ETL solution

Re: Updating column in merged dataset based on comparison with old dataset

OLUGBOJI — Thu, 14 Feb 2019 18:52:38 GMT

I can see that this 'ifn(count(*)=1,startdate,.)' will not work if the teacher takes up the class again in future. Thanks @ErikLund_Jensen

Re: Updating column in merged dataset based on comparison with old dataset

OLUGBOJI — Thu, 14 Feb 2019 19:15:37 GMT

I tried to add a dataset for march and I'm not getting results.

Re: Updating column in merged dataset based on comparison with old dataset

OLUGBOJI — Thu, 14 Feb 2019 19:18:17 GMT

I tried adding a march dataset and it didn't work for the following scenarios:
1. Teacher with id 1 starts taking class b by march
2.March dataset merged with 'January' and 'february'

Re: Updating column in merged dataset based on comparison with old dataset

novinosrin — Thu, 14 Feb 2019 19:53:29 GMT

Yes i know. Hence the reason i admitted I made a mistake. Just got back to my computer. Let me take a closer look

Re: Updating column in merged dataset based on comparison with old dataset

novinosrin — Thu, 14 Feb 2019 22:51:11 GMT

Hi @OLUGBOJI

data jan;
infile cards truncover;
input id className $ startdate  :ddmmyy10.       enddate :ddmmyy10. ;
format startdate  ddmmyy10.       enddate ddmmyy10. ;
cards;
1   A               01/01/2019
1   B               01/01/2019
2   A               01/01/2019
3   B               01/01/2019
;

data feb;
infile cards truncover;
input id    className $  startdate  :ddmmyy10.     enddate  :ddmmyy10. ;
format startdate  ddmmyy10.       enddate ddmmyy10. ;
cards;
1       A               01/02/2019
2       A                01/02/2019
3       B                01/02/2019
;

data mar;
infile cards truncover;
input id    className $  startdate  :ddmmyy10.     enddate  :ddmmyy10. ;
format startdate  ddmmyy10.       enddate ddmmyy10. ;
cards;
1   A             01/03/2019
1   B               01/03/2019
3   B            01/03/2019
;

/*Look up Jan to feb*/
data jan_to_feb ;
if _n_=1 then do;
   dcl hash H (dataset:'feb',multidata:'y') ;/*replace subsequent months like march here*/
   h.definekey  ("id","classname") ;
   h.definedata ("startdate", "enddate") ;
   h.definedone () ;
   dcl hiter hh('h');
end;
set jan end=lr;/*jan is like your old full load*/
by id classname;
if last.classname then 
do;
	rc=h.check();
	if rc=0 then do;
		output;
		do rc= h.find() by 0 while(rc=0);
			output;
			rc=h.find_next();
		end;
		end;
	else do; enddate=startdate;output;end;
end;
else output;
drop rc;
run;

RESULTS:

id className startdate enddate 
1 A 01/01/2019 . 
1 A 01/02/2019 . 
1 B 01/01/2019 01/01/2019 
2 A 01/01/2019 . 
2 A 01/02/2019 . 
3 B 01/01/2019 . 
3 B 01/02/2019 .

Now moving to the 2nd look up the previous result with March

/*Look up Janfeb(the output of previous
to march*/
data jan_to_feb___to_mar ;
if _n_=1 then do;
   dcl hash H (dataset:'mar',multidata:'y') ;/*replace subsequent months like march here*/
   h.definekey  ("id","classname") ;
   h.definedata ("startdate", "enddate") ;
   h.definedone () ;
   dcl hiter hh('h');
end;
set jan_to_feb end=lr;/*jan_to_feb is like your old full load*/
by id classname;
if last.classname then 
do;
	rc=h.check();
	if rc=0 then do;
		output;
		do rc= h.find() by 0 while(rc=0);
			output;
			rc=h.find_next();
		end;
		end;
	else do; enddate=startdate;output;end;
end;
else output;
drop rc;
run;

RESULTS:

id className startdate enddate 
1 A 01/01/2019 . 
1 A 01/02/2019 . 
1 A 01/03/2019 . 
1 B 01/01/2019 01/01/2019 
1 B 01/03/2019 . 
2 A 01/01/2019 . 
2 A 01/02/2019 01/02/2019 
3 B 01/01/2019 . 
3 B 01/02/2019 . 
3 B 01/03/2019 .

Re: Updating column in merged dataset based on comparison with old dataset

ErikLund_Jensen — Fri, 15 Feb 2019 10:26:09 GMT

Hi OLUGBOJI

My solution does not work if a teacher starts taking a class again, because the teacher is still active, so max(startdate) will be march. But what should happen? - It often happens to me that I give a solution to a specific SAS coding problem without solving the user's real problem, because I haven't got the overall picture.

You might get better help if you gave a short explanation of your intentions with the program. Marking the months where a teacher is not present in a given class, allocating same teacher to new classes if a course is finished, or??

Re: Updating column in merged dataset based on comparison with old dataset

tomrvincent — Fri, 15 Feb 2019 13:04:59 GMT

Here's how I approached it:

PROC SQL;
CREATE TABLE WORK.QUERY_FOR_JAN AS
SELECT t1.id,
t1.className,
t1.startdate,
t2.id AS id1,
t2.className AS className1,
t2.startdate AS startdate1,
t3.id AS id2,
t3.className AS className2,
t3.startdate AS startdate2,
/* enddate */
(case when t2.startdate is missing then t1.startdate when t3.startdate is missing then t2.startdate end)
FORMAT=date9. AS enddate
FROM WORK.JAN t1
LEFT JOIN WORK.FEB t2 ON (t1.id = t2.id) AND (t1.className = t2.className)
LEFT JOIN WORK.MAR t3 ON (t2.id = t3.id) AND (t2.className = t3.className);
QUIT;

Re: Updating column in merged dataset based on comparison with old dataset

OLUGBOJI — Fri, 15 Feb 2019 14:36:58 GMT

I'm sorry that I didn't make myself clear. Teachers might be making claims for classes they only taught in the past and thereby claim bonus payments. Bonus payment goes out every quarter. The intentions of the program is to verify the output against claims made by the teachers.

Re: Updating column in merged dataset based on comparison with old dataset

ErikLund_Jensen — Fri, 15 Feb 2019 17:04:42 GMT

@OLUGBOJI

Thank you.

If your purpose is to get an overview to verify claims, and you don't need the data for further processing, I think a report is all you need, Proc Tabulate can do it direct from your input data without further data processing except for adding q quarter variable to use as class variable in Proc Tabulate.

I added an extra month to data from the previous examples to have data for more than one quarter. The tables have two sets of columns: Quarters with counts pr. quarter and Months as documentation for the counts:


data apr;
infile cards truncover;
input id    className $  startdate  :ddmmyy10.     enddate  :ddmmyy10. ;
format startdate  ddmmyy10.       enddate ddmmyy10. ;
cards;
1   A             01/04/2019
1   B               01/04/2019
4   B            01/04/2019
;

data all; set jan feb mar apr;
	Quarter = put(startdate,yyqd.);
run;

proc tabulate data=all;
	class id classname Quarter startdate;
	format startdate yymmd8.;
	label startdate = 'Month';
	keylabel N=' ' all=' ';
	table id*classname,Quarter all *startdate /box = 'Teacher by class' rts=20;
	table id,Quarter all *startdate  /box = 'Teacher total' rts=20;;
run;

Proc tabulate writes 2 tables:

Re: Updating column in merged dataset based on comparison with old dataset

ErikLund_Jensen — Fri, 15 Feb 2019 17:10:12 GMT

Sorry - The tables were not made from april data as shown,because ID 4 had start in March. This is made from correct input: