topic Re: Using TSQL in SAS and issue with CASE Statement in SAS Programming

Using TSQL in SAS and issue with CASE Statement

TMiller16 — Wed, 23 Nov 2016 17:54:43 GMT

I have data imputs in my data that I need to change.

The first one is CarMax # 10000 and the other is DriveTime 10000

I want all instances of CarMax # 100000 to be "CarMax" and DriveTime 10000 to be "DriveTime".

They both have different locations so it could be 100001, 100002 and so on same for DriveTime.

CASE WHEN Sales.Dlr_Name LIKE 'CarMax%'
THEN "CarMax"
WHEN LEFT (Sales.Dlr_Name,9)
THEN "Drivetime"
ELSE Sales.Dlr_Name
END AS Dlr_Name,

When I use this it only returns CarMax in the results

Help, please

Re: Using TSQL in SAS and issue with CASE Statement

TMiller16 — Wed, 23 Nov 2016 17:56:33 GMT

I mixed up the codes from when I pasted it, was trying either the LIKE or the LEFT

LIKE only brought back Carmax, LEFT did the same thing.....

Re: Using TSQL in SAS and issue with CASE Statement

Haikuo — Wed, 23 Nov 2016 18:04:28 GMT

Your syntax is a little off, you will need two 'CASE WHEN .... END' for two variables.

Re: Using TSQL in SAS and issue with CASE Statement

TMiller16 — Wed, 23 Nov 2016 19:02:03 GMT

Right, like I said, I copied the "in progress" one. Here it is:

CASE WHEN Sales.Dlr_Name LIKE 'CarMax%'
THEN "CarMax"
CASE WHEN Sales.Dlr_Name LIKE 'Drivetime%'
THEN "Drivetime"
ELSE Sales.Dlr_Name
END AS Dlr_Name,

Re: Using TSQL in SAS and issue with CASE Statement

Haikuo — Wed, 23 Nov 2016 19:07:35 GMT

Give a few examples of your incoming data and your expected outcomes, if you could.

Re: Using TSQL in SAS and issue with CASE Statement

TMiller16 — Wed, 23 Nov 2016 19:13:11 GMT

CARMAX #7157
CARMAX #7521
CARMAX AUTO SUPERSTORES INC

These need to change to CarMax

DRIVETIME CAR SALES (PHOENIX< AZ)
DRIVETIME-BUYING ACCT

Should be DRIVETIME

Re: Using TSQL in SAS and issue with CASE Statement

Haikuo — Wed, 23 Nov 2016 19:25:58 GMT

So this is from one variable called " Dlr_Name "?

Re: Using TSQL in SAS and issue with CASE Statement

TMiller16 — Wed, 23 Nov 2016 19:38:08 GMT

Yes it is, this is what I have tried so far:

CASE WHEN LEFT (Sales.Dlr_Name,6) = "CARMAX"
THEN "CARMAX"
WHEN LEFT (Sales.Dlr_Name,9) = "DRIVETIME"
THEN "DRIVETIME"
ELSE Sales.Dlr_Name
END AS Dlr_Name,

Re: Using TSQL in SAS and issue with CASE Statement

Haikuo — Wed, 23 Nov 2016 19:51:01 GMT

If you are using explicit pass-through method to access T-SQL from SAS, I have no idea what happened. You seem to have coded just right. If you are not using pass-through, then you will need change your code to the SAS SQL style. for example:

case when upcase(left(Sales.Dlr_Name)) eqt 'CARMAX' THEN 'CARMAX'
     WHEN upcase(left(Sales.Dlr_Name)) eqt 'DRIVETIME' then 'DRIVETIME'
	 ELSE Sales.Dlr_Name
END AS Dlr_Name

or using substr, like, contain, find or index functions.

Re: Using TSQL in SAS and issue with CASE Statement

TMiller16 — Wed, 23 Nov 2016 20:07:18 GMT

Thank you! I added the UPCASE and the () around LEFT() and used EQT instead of = and bingo in business.