topic Re: Delete Almost Duplicate Rows in SAS Enterprise Guide

Delete Almost Duplicate Rows

deleted_user — Fri, 18 Jun 2010 20:39:23 GMT

I need help from the experts!

What I have done, is create a query for a full enterprise employee list within EG. I do not believe there are any primary keys in hte table. The problem is that if anything changes with the employee, it creates a new entry. Therefore I have a tons of entries for each employee.

I have created two sorts. One to sort the payroll_id # by ascending and one to sort the mod_date by decending so the last date modified is on top.

I am brand spanking new to SAS and EG. I am not sure how to get the duplicate entries out from here. I have spent hours now on this and am totally confused. Can anyone out there help?

Dean

Re: Delete Almost Duplicate Rows

deleted_user — Fri, 18 Jun 2010 23:57:31 GMT

Forgot to mention that I am running EG 4.1...

Re: Delete Almost Duplicate Rows

Patrick — Sat, 19 Jun 2010 18:37:21 GMT

Not sure how to do this using the EG wizards.

Below a code example. I believe you've done the first 2 steps already (just sort by payroll_id and mod_date - both ascending).

The last step then picks the last observation withing a payroll_id (the last modified entry). Hope this is what you're after.

/* create some sample data */
data have;
format payroll_id 8.;
format mod_date date9.;
do payroll_id=1 to 10;
do mod_date='01jan2010'd to '31mar2010'd;
example_changing_var+1;
if ceil(ranuni(1)*100) le 5 then output;
end;
end;
run;

/* sort by payroll_id and mod_date */
proc sort data=have out=haveSorted;
by payroll_id mod_date;
run;

/* pick the last observation per payroll_id */
data want;
set haveSorted;
by payroll_id;
if last.payroll_id then output;
run; Message was edited by: Patrick

Re: Delete Almost Duplicate Rows

deleted_user — Sat, 19 Jun 2010 22:00:52 GMT

Okay... i did use your code... and it did produce something... but not quite sure what is produced... i tried a couple of different things to modify to try to get it to work for me... but it wouldn't take out the duplicate entries...

I will post my Query PROC SQL code that came from Enterprise Guide... mabye you can make more sense of what i need to do by seeing the code that is already generated.

%_eg_conditional_dropds(WORK.Query_for_agent_dgas_final);

PROC SQL;
CREATE TABLE WORK.Query_for_agent_dgas_final AS SELECT DISTINCT agent_dgas_final.mod_date,
agent_dgas_final.EMPLOYEE_NUMBER,
agent_dgas_final.First_Name,
agent_dgas_final.Last_Name,
agent_dgas_final.Department,
agent_dgas_final.Agent_Job_Class,
agent_dgas_final.Office_ID,
agent_dgas_final.Supr_ID,
agent_dgas_final.Agent_login,
agent_dgas_final.EMPLOYEE_TYPE,
agent_dgas_final.HIRE_DATE,
agent_dgas_final.TERMINATION_DATE,
agent_dgas_final.CRTD_CMSID
FROM EC100020.AGENT_DGAS_FINAL AS agent_dgas_final
WHERE agent_dgas_final.EMPLOYEE_NUMBER NOT IS NULL AND agent_dgas_final.EMPLOYEE_NUMBER > "000002"
GROUP BY agent_dgas_final.EMPLOYEE_NUMBER
ORDER BY agent_dgas_final.EMPLOYEE_NUMBER, agent_dgas_final.mod_date;
QUIT;

That gives me the list that i need... just gives me about 70-200 entries per agent... versus the one i am looking for. I swear I will learn this backwards and forwards... the language syntax is so much different from access... and its really confusing to me..

Dean

Message was edited by: Dean_Z Message was edited by: Dean_Z

Re: Delete Almost Duplicate Rows

deleted_user — Sun, 20 Jun 2010 02:27:52 GMT

WOW...I finally figured it out... and this is what i did...

Once i had my query completed... i right clicked on results and did a sort data... from there i put in the Employee_ID into the sort area... then clicked options on the left and chose to only display the first line. In my sort on the original query, I changed the date back to decending so the first line was the last modified line in the data...

Then I ran the query and walla, the data magically appeared exactly the way i wanted. Thanks for your help Patrick... after looking over your code a thousand times, i finally figured out what to do... thanks!!!!!!!

Dean