topic Removing missing values in a group in SAS Programming

Removing missing values in a group

yashpande — Thu, 30 Apr 2015 13:11:46 GMT

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

My EMP table looks like this

Emp_Name	Address	Phone	Dept
abc		1234
abc	park road
abc			Sales

What I want in output is single observation like

Emp_Name	Address	Phone	Dept
abc	park road	1234	Sales

Need direction please

Re: Removing missing values in a group

Astounding — Thu, 30 Apr 2015 13:37:43 GMT

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.

Re: Removing missing values in a group

RW9 — Thu, 30 Apr 2015 13:45:38 GMT

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;

Re: Removing missing values in a group

normarc — Thu, 30 Apr 2015 21:31:54 GMT

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?