How do I UPDATE column with CASE WHEN in existing table

bc123456 — Mon, 15 Jun 2020 16:01:23 GMT

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,

Re: How do I UPDATE column with CASE WHEN in existing table

mklangley — Mon, 15 Jun 2020 16:36:34 GMT

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;

topic Re: How do I UPDATE column with CASE WHEN in existing table in SAS Programming

How do I UPDATE column with CASE WHEN in existing table

Re: How do I UPDATE column with CASE WHEN in existing table