Using TSQL in SAS and issue with CASE Statement

[TMiller16]

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

[TMiller16]

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.....

[Haikuo]

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

[TMiller16]

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,

[Haikuo]

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

[TMiller16]

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

[Haikuo]

So this is from one variable called " Dlr_Name "?

[TMiller16]

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,

[Haikuo · Accepted solution]

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.

[TMiller16]

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