I have the following data set:
ID |
Co Name |
12345 |
ABC COMPANY |
12345 |
Abc company |
Is it possible to create a new computed column called “New Name” in SAS EG query builder to take one of the “Co Name” when the ID matches? also there are data fields that 'ID' is null but has a 'Co Name'. I would like to leave those data field as is.
Thanks
Matches what? As in: "Is it possible to create a new computed column called “New Name” in SAS EG query builder to take one of the “Co Name” when the ID matches?" And since you are showing multiple values of "Co Name" for the same Id, which specific "Co name" would be the result?
Your comment about "there are data fields that 'ID' is null but has a 'Co Name'. I would like to leave those data field as is. " makes me ask for a bit more of an example, what your matching and where it resides and what the result of the process given the shown example data should be.
hope this helps to clarify.
Original data:
ID |
Co Name |
12345 |
ABC COMPANY |
12345 |
Abc company |
|
Xyz Company |
|
XYZ COMPANY |
67890 |
asd Co |
Output data:
ID |
NEW Co Name |
12345 |
ABC COMPANY |
|
Xyz Company |
|
XYZ COMPANY |
67890 |
asd Co |
Below the SQL you need to create via point&click
proc sql;
select distinct
id,
case
when not missing(id) then upcase(co_name)
else co_name
end
as new_name
from have
;
quit;
Additional clarification. I would like the “NEW Co Name” column to output the “Co Name” with the most occurrences.
Original data:
ID |
Co Name |
12345 |
ABC COMPANY |
12345 |
ABC COMPANY |
12345 |
ABC COMPANY |
12345 |
Abc company |
12345 |
Abc company |
|
Xyz Company |
|
XYZ COMPANY |
67890 |
asd Co |
Output data:
ID |
NEW Co Name |
12345 |
ABC COMPANY |
|
Xyz Company |
|
XYZ COMPANY |
67890 |
asd Co |
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.