02-26-2016 01:49 PM - last edited on 02-26-2016 03:44 PM by Reeza
I have a code as below.
But my output shows like same pidm twice with student and applicant . I want to shows if it is duplicate take the only student.
So my output as below:
pidm statusstudent gender StudentApplicant
102 Student F
108 Student M
108 M Applicant
I need this
pidm statusstudent gender
102 Student F
108 Applicant M
But i want to do this inside this code i wrote. Not a saperate code. I need to append this code? Is that possible or not? I will delete my other post. Thank you
CREATE TABLE SGBSTDN AS SELECT DISTINCT a.SGBSTDN_PIDM AS PIDM, 'Student' as StatusStudent, case when b.Spbpers_sex IS NULL then "N" when b.Spbpers_sex ='F' then "F" when b.Spbpers_sex ='M' then "M" end as gender FROM STG.SGBSTDN as a left join stg.spbpers as b on b.spbpers_pidm=a.SGBSTDN_PIDM outer union corr SELECT DISTINCT d.SARADAP_PIDM AS PIDM, 'Applicant' as StatusApplicant, case when c.Spbpers_sex IS NULL then "N" when c.Spbpers_sex ='F' then "F" when c.Spbpers_sex ='M' then "M" end as gender FROM STG.SARADAP as d left join stg.spbpers as c on c.spbpers_pidm=d.SARADAP_PIDM ORDER BY PIDM ;QUIT;
02-26-2016 03:48 PM
You can use COALESCE() to get the applicant/student status:
coalescec(statusstudent, StudentApplicant) as variable_name
To remove the records, ie take the Student over Applicant you'll have to write some additional logic. Personally, I don't prefer to have long SQL codes for multiple calculations because it gets unwieldy and hard to debug.
A data step would be relatively easy to get the first or last record of multiple ID's.