DATA Step, Macro, Functions and more

Removing missing values in a group

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

Removing missing values in a group

This could probably be the really novice task but i am unable to get the answer easily.

My EMP table looks like this

Emp_NameAddressPhoneDept
abc 1234
abcpark road
abc Sales

What I want in output is single observation like

Emp_NameAddressPhoneDept
abcpark road1234Sales

Need direction please


Accepted Solutions
Solution
‎04-30-2015 09:45 AM
Super User
Super User
Posts: 7,977

Re: Removing missing values in a group

Posted in reply to yashpande

You can also do the same thing by merging:

data have;

  infile datalines dlm="," dsd;

  input Emp_Name $ Address $ Phone $ Dept $;

datalines;

abc,,1234,

abc,park road,,

abc,,,Sales

;

run;

data want;

  merge have (keep=emp_name address where=(address ne ""))

        have (keep=emp_name phone where=(phone ne ""))

        have (keep=emp_name dept where=(dept ne ""));

  by emp_name;

run;

View solution in original post


All Replies
Super User
Posts: 5,516

Re: Removing missing values in a group

Posted in reply to yashpande

It's short, but understanding it might require some study on your part.  Assuming your data set is sorted by EMP_NAME:

data want;

   update emp (obs=0) emp;

   by emp_name;

run;

Good luck.

Solution
‎04-30-2015 09:45 AM
Super User
Super User
Posts: 7,977

Re: Removing missing values in a group

Posted in reply to yashpande

You can also do the same thing by merging:

data have;

  infile datalines dlm="," dsd;

  input Emp_Name $ Address $ Phone $ Dept $;

datalines;

abc,,1234,

abc,park road,,

abc,,,Sales

;

run;

data want;

  merge have (keep=emp_name address where=(address ne ""))

        have (keep=emp_name phone where=(phone ne ""))

        have (keep=emp_name dept where=(dept ne ""));

  by emp_name;

run;

Occasional Contributor
Posts: 5

Re: Removing missing values in a group

Posted in reply to yashpande

This is a very simple example.   My question is what business rules do you want to employ when you find something like multiple address for a given employee?   If you had something like a status/update date, then perhaps you could sort on that to help choose?

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 248 views
  • 6 likes
  • 4 in conversation