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
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;
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.
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;
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?
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.