DATA Step, Macro, Functions and more

Append code

Posts: 34

Append code

[ Edited ]


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;
Super User
Posts: 23,754

Re: Append code

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. 



Ask a Question
Discussion stats
  • 1 reply
  • 2 in conversation