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?
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 */
format payroll_id 8.;
format mod_date date9.;
do payroll_id=1 to 10;
do mod_date='01jan2010'd to '31mar2010'd;
if ceil(ranuni(1)*100) le 5 then output;
/* sort by payroll_id and mod_date */
proc sort data=have out=haveSorted;
by payroll_id mod_date;
/* pick the last observation per payroll_id */
if last.payroll_id then output;
Message was edited by: Patrick
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.
CREATE TABLE WORK.Query_for_agent_dgas_final AS SELECT DISTINCT agent_dgas_final.mod_date,
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;
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..
Message was edited by: Dean_Z
Message was edited by: Dean_Z
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!!!!!!!