topic Re: SAS Base- Loop through different dataset to check if some fields are the same and overwrite in New SAS User

SAS Base- Loop through different dataset to check if some fields are the same and overwrite

lucazanotti — Mon, 08 Oct 2018 18:57:54 GMT

Dear suppport,

i hope you can help me in this. I have the following datasets ("info" and "score").

id	id2	faculty	name	surname	course
1	x	science	mark	red	math
2	y	science	lewis	green	math
3	z	eng	joshua	yellow	math
3	z	eng	joshua	yellow	english

score

id	id2	name	surname	course	mark
1	x	mark	red	math	5
3	x	mark	red	english	6
3	z	joshua	yellow	math	7
3	z	joshua	yellow	english	8
4	z	joshua	yellow	art	9

What i would like to achieve is to insert some information in "info" that recursively check based on some criteria if a specific entry on the "infor" is on score and if not append the new result in "infor". In detail it should check if Name and surname and id2 are in the score and only if the course is different append that line in "infor". My issue is that i have tried to do a data merge (and dropping one value) but unsuccessfull as the id field and faculty is wrong (not aligned with the infor or empty) .

The expected result should be like this:

id	id2	faculty	name	surname	course
1	x	science	mark	red	math
2	y	science	lewis	green	math
3	z	eng	joshua	yellow	math
3	z	eng	joshua	yellow	english
1	x	science	mark	red	english
3	z	eng	joshua	yellow	art

with the last two observation inserted accordingly.

I wonder if is possible to loop through the dataset and check/compare (with if statements) and proceed to create the observation.

If you can post a link or guide me through into the right direction it would be amazing.

Many thanks

Luca

Re: SAS Base- Loop through different dataset to check if some fields are the same and overwrite

Astounding — Mon, 08 Oct 2018 19:23:02 GMT

Sort and Merge can handle this with no problem. If you ran into difficulties, you might have to add variables to the BY statement:

by id id2 name surname course;

It could take all five variables to determine match vs. mismatch.

Re: SAS Base- Loop through different dataset to check if some fields are the same and overwrite

novinosrin — Mon, 08 Oct 2018 19:53:19 GMT


proc sql;
create table want as
select distinct *
from
(select id	,id2	,	name ,	surname,	course from info
union 
select id	,id2	,	name ,	surname,	course from score);
quit;

Re: SAS Base- Loop through different dataset to check if some fields are the same and overwrite

lucazanotti — Fri, 12 Oct 2018 08:22:53 GMT

Hi,

first of all many thanks for the reply.

I think i am going to use the first approach of merging and sorting across the variables as the UNION SQL cannot distinguish which rowe (giving the criteria described) need to be kept or not.

Luca