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
PROC SQL;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.