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