I have to switch the values of APPROVAL and REFERRAL depending on date.For each id the first date value should be approval and second date value should be Refferal,only if approval and refferal are present for perticular id.
This is a solution:
input id date ANYDTDTE10. field $ Status $ 25-32;
format date date7.;
33822730 12/27/2007 xyz Approval
33822730 2/3/2009 xyz
33822730 2/4/2009 xyz Referral
33822730 2/5/2009 xyz
proc sort data=i;
by id date;
if FIRST.id and UPCASE(Status) in ("APPROVAL" "REFERRAL") then s="Referral";
else if UPCASE(Status) in ("APPROVAL" "REFERRAL") then s="Approval";
The query wizard in EG generates SQL code.
Writing logic in SQL where you need to do things on a "first" and "second" row is often tricky. Procedural languages like Base SAS are much better suited for this.
So even with the best (SQL) query wizard it would be rather hard to set up this query.
Why not use the possibilities EG gives you - which in this case would be the programming window.