topic Re: Replacing values in multiple columns using case statement in SQL in SAS Programming

Replacing values in multiple columns using case statement in SQL

Arvind1 — Wed, 08 Jan 2020 17:40:56 GMT

Dear all,

I have a table where in I have to replace value in two columns ( Customer_Complaint and Status) based on the value present in them. I tried using SQL with CASE statement to modify the variables. However i am able to update values only in one particular column (Customer_Complaint) and not in other Status column.

Is there a way to update data in both columns ? I tried the below code, however i am getting an error when using multiple case statements.

Table structure : Ticket /Customer_Complaint/ Status

proc sql;
select * ,
case
when customer_complaint like '%Speed%' then 'Speed'
when customer_complaint like '%Service%' then 'Service'
when customer_complaint like '%Data%' then 'Data'
else 'Miscellaneous'
end as Customer_Complaint1

Case

when status like 'Pending' then 'Closed'

end as Status_1
from work.comcast;

quit;

Is there a way that i first update the data in the first column (Customer_Complaint), write into a temporary file and then perform operation on the temporary file to update the Status column?

or is there a way to update file using replace? Please guide

thanks in advance for your help.

Re: Replacing values in multiple columns using case statement in SQL

Krueger — Wed, 08 Jan 2020 17:43:58 GMT

You are missing a comma after Customer_Complaint1 which I added in. This would cause only 1 row to appear assuming it would run successfully.

You might also want to add an ELSE to your CASE for Status_1.

proc sql;
select * ,
case
when customer_complaint like '%Speed%' then 'Speed'
when customer_complaint like '%Service%' then 'Service'
when customer_complaint like '%Data%' then 'Data'
else 'Miscellaneous'
end as Customer_Complaint1,

Case 

when status like 'Pending' then 'Closed'

end as Status_1
from work.comcast;

quit;

Re: Replacing values in multiple columns using case statement in SQL

PaigeMiller — Wed, 08 Jan 2020 17:44:08 GMT

@Arvind1 wrote:

Dear all,

I have a table where in I have to replace value in two columns ( Customer_Complaint and Status) based on the value present in them. I tried using SQL with CASE statement to modify the variables. However i am able to update values only in one particular column (Customer_Complaint) and not in other Status column.

Is there a way to update data in both columns ? I tried the below code, however i am getting an error when using multiple case statements.

What error?

Show us the log (the code in the log as well as the error messages in the log)

Re: Replacing values in multiple columns using case statement in SQL

Arvind1 — Wed, 08 Jan 2020 17:59:58 GMT

Thank you Kruger. That helped a lot . Am able to update the values in both the columns.

However one more doubt, is there any other alternate way to update the values in the columns when importing or reading the file using replace ?

Re: Replacing values in multiple columns using case statement in SQL

Arvind1 — Wed, 08 Jan 2020 18:01:27 GMT

Thanks Paige . The solution has been arrived. However would like to know if there is an alternate way of replacing the data in the file other than the sql method.

Re: Replacing values in multiple columns using case statement in SQL

Tom — Wed, 08 Jan 2020 18:45:07 GMT

How are you reading the data. I should be simple to write DATA STEP code to do what ever you want.

If you are using a data step to create the original file then just add the logic to that step.

data comcast;
  ... whatever logic you currently have that creates CUSTOMER_COMPLAINT and STATUS ...
  Customer_Complaint1= 'Miscellaneous';
  select ;
    when (index(customer_complaint,'Speed')) Customer_Complaint1='Speed';
    when (index(customer_complaint,'Service')) Customer_Complaint1='Service';
    when (index(customer_complaint,'Data')) Customer_Complaint1='Data';
    otherwise ;
  end;
  if status='Pending' then Status_1='Closed';
run;

Re: Replacing values in multiple columns using case statement in SQL

Reeza — Wed, 08 Jan 2020 18:54:32 GMT

I'd recommend a data step and listing the words in a temporary array and then looping through the temporary array. That way if your word list gets longer you're only updating the list once.

What happens if a statement has more than one of the keywords?

Re: Replacing values in multiple columns using case statement in SQL

Arvind1 — Thu, 09 Jan 2020 02:47:09 GMT

Thanks everyone for your responses.

To give a background, i am doing a data clean up of the columns - Customer complaint and Status. When there is a word speed in the customer complaint i would want to mark it as speed issues and when there is poor customer service i would want to mark it as service issue etc. rest of the complaints would be miscellaneous.

Other than the SQL method is there anyother way that i can replace the data in the file. Attaching the file for your reference and the working SQL query.

FILENAME REFFILE "/home/u44638328/Comcast.csv";

proc import datafile=REFFILE
DBMS=CSV
OUT=work.Comcast replace;
getnames=YES;
run;

proc sql;
select * ,
case
when customer_complaint like '%Speed%' then 'Speed'
when customer_complaint like '%speed%' then 'Speed'
when customer_complaint like '%Service%' then 'Service'
when customer_complaint like '%service%' then 'Service'
when customer_complaint like '%Data%' then 'Data'
when customer_complaint like '%data%' then 'Data'
when customer_complaint like '%billing%' then 'Billing'
when customer_complaint like '%Billing%' then 'Billing'
when customer_complaint like '%Price%' then 'Pric'
when customer_complaint like '%Outage%' then 'Outage'
when customer_complaint like '%outage%' then 'Outage'
else 'Miscellaneous'
end as Customer_Complaint1,

Case
when status like '%end%' then 'Open'
when status like '%ved%' then 'Closed'
when status like 'Closed' then 'Closed'
when status like 'Open' then 'Open'

end as Status_1
from work.comcast;

quit;

Re: Replacing values in multiple columns using case statement in SQL

Krueger — Thu, 09 Jan 2020 15:26:21 GMT

I'm new to SAS so unsure if this is going to run as expected but here's doing it as a datastep. You can use upcase() or lowcase() to remove some of your duplicate code.

data want;
	set work.comcast;
	if upcase(customer_complaint) = 'SERVICE' then Customer_Complaint1='Service';
	else if upcase(customer_complaint) = 'DATA' then Customer_Complaint1='Data';
	else if upcase(customer_complaint) = 'BILLING' then Customer_Complaint1='Billing';
	else if upcase(customer_complaint) = 'PRICE' then Customer_Complaint1='Price';
	else if upcase(customer_complaint) = 'OUTAGE' then Customer_Complaint1='Outage';
	else Customer_Complaint1='Miscellaneous';

	if upcase(status) = 'END' then Status_1 = 'Open';
	else if upcase(status) = 'VED' then Status_1 = 'Closed';
	else if upcase(status) = 'CLOSED' then Status_1 = 'Closed';
	else if upcase(status) = 'OPEN' then Status_1 = 'Open';
run;

Some example data would also be helpful. I'm assuming the above isn't going to fully work as it appears your searching a string of text. In that case you will likely need to experiment with the find() function in SAS.

Edit: Instead of find() you might have to do =: in place of like.