DATA Step, Macro, Functions and more

Using TSQL in SAS and issue with CASE Statement

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

Using TSQL in SAS and issue with CASE Statement

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


Accepted Solutions
Solution
‎11-23-2016 03:05 PM
Respected Advisor
Posts: 3,124

Re: Using TSQL in SAS and issue with CASE Statement

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.

 

View solution in original post


All Replies
Contributor
Posts: 35

Re: Using TSQL in SAS and issue with CASE Statement

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

Respected Advisor
Posts: 3,124

Re: Using TSQL in SAS and issue with CASE Statement

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

 

 

 

Contributor
Posts: 35

Re: Using TSQL in SAS and issue with CASE Statement

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,
Respected Advisor
Posts: 3,124

Re: Using TSQL in SAS and issue with CASE Statement

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

Contributor
Posts: 35

Re: Using TSQL in SAS and issue with CASE Statement

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



Respected Advisor
Posts: 3,124

Re: Using TSQL in SAS and issue with CASE Statement

So this is from one variable called " Dlr_Name "?

Contributor
Posts: 35

Re: Using TSQL in SAS and issue with CASE Statement

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,
Solution
‎11-23-2016 03:05 PM
Respected Advisor
Posts: 3,124

Re: Using TSQL in SAS and issue with CASE Statement

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.

 

Contributor
Posts: 35

Re: Using TSQL in SAS and issue with CASE Statement

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 259 views
  • 0 likes
  • 2 in conversation