BookmarkSubscribeRSS Feed
user24
Obsidian | Level 7

 

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;
1 REPLY 1
Reeza
Super User

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. 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 840 views
  • 0 likes
  • 2 in conversation