Hi All,
I'm new to SAS and I'm currently working on a master dataset, pulling in over 500K rows. I have a column that I would like to update the values to more "generic" descriptors. For example, the column name is 'ServiceDescription' and an example of a value within this column is "FedEx Exconomy". I'd like to UPDATE the current table, modifying the current value to show "Standard" rather than "FedEx Economy" when this value appears in a row. I currently have something like this:
PROC SQL;
UPDATE TMP.FINAL
SET ServiceDescription=CASE
WHEN ServiceDescription = 'Ground' THEN 'FlatRate'
WHEN ServiceDescription = 'FedEx Economy' THEN 'Standard'
WHEN ServiceDescription = 'FedEx 2Day' THEN '2Day'
WHEN ServiceDescription = 'FedEx 2Day AM' THEN '2Day AM'
WHEN ServiceDescription = 'FedEx Standard Overnight' THEN 'Standard Overnight'
WHEN ServiceDescription = 'FedEx Priority Overnight' THEN 'Priority Overnight'
WHEN ServiceDescription = 'FedEx First Overnight' THEN 'First Overnight'
WHEN ServiceDescription = 'FedEx International Priority' THEN 'International Priority'
ELSE ServiceDescription /* Does not modify existing values when conditions are not met */
END AS ServiceDescription; /* Does not create a new column */
QUIT;
I know there are pieces missing, and have exhausted all existing resources available. Any and all help would be greatly appreciated.
Thanks,
B
This is a different approach, but you could do it in a left join:
data new;
input ServiceDescription & $28. new_ServiceDescription & $22.;
datalines;
Ground FlatRate
FedEx Economy Standard
FedEx 2Day 2Day
FedEx 2Day AM 2Day AM
FedEx Standard Overnight Standard Overnight
FedEx Priority Overnight Priority Overnight
FedEx First Overnight First Overnight
FedEx International Priority International Priority
;
run;
proc sql;
create table want as
select a.other_columns /* other columns, sans ServiceDescription */
,case
when b.ServiceDescription is not null
then b.new_ServiceDescription
else a.ServiceDescription
end as ServiceDescription
from TMP.FINAL a
left join new b
on a.ServiceDescription = b.ServiceDescription
;
quit;
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.