New addition to SAS

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

New addition to SAS

I have to created a computed field and running into syntax issues, Formal SQL user.

 

PROC SQL NOEXEC;

SELECT (CASE Sales.Program IfNull THEN "Repo" ELSE Sales.Program "Retail" OR "Repo" ELSE Sales.Program

"Regular Dealer" OR "Repo" ELSE Sales.Program "Insurance Recovery" OR "Repo" ELSE Sales.Program

"Manufacturer/Factory" OR "Repo" ELSE Sales.Program) AS CALCULATION

FROM WORK.RDW1 t1

INNER JOIN WORK.RDW2 t2 ON (t1.Auc_Name = t2.Auc_Name);

QUIT;

 

I am sure I am doing this all wrong, so any help would be appreciated.


Accepted Solutions
Solution
‎10-14-2016 04:02 PM
Grand Advisor
Posts: 17,478

Re: New addition to SAS

It looks like you're trying to implement a CASE statement?

 

Here's the SAS reference, it's typically 

 

CASE

 when <condition> then <value>

when <condition> then <value>

else <value>

END as NEW_VARIABLE_NAME

 

Also, if you want to check for multiple values, ie x = 1 or x=2 or x=3 you can use IN so it becomes

x in (1, 2, 3) and similarily for character variables.

 

http://support.sas.com/documentation/cdl/en/sqlproc/69049/HTML/default/viewer.htm#n0a85s0ijz65irn1h3...

 

I can't quite understand your logic so hopefully this is enough for you to translate this to SAS. 

View solution in original post


All Replies
Grand Advisor
Posts: 10,262

Re: New addition to SAS

It is often very helpful to post the log of the code and any messages to disagnose things.

If you were running this in SAS and not as pass through to another DBMS I am going to guess that you get an error around IFNULL.

IFNULL is not SAS syntax.

 

Second a SAS CASE clause is going to look like this:

          case
             when Latitude gt 67 then 'North Frigid'
             when 67 ge Latitude ge 23 then 'North Temperate'
             when 23 gt Latitude gt -23 then 'Torrid'
             when -23 ge Latitude ge -67 then 'South Temperate'
             else 'South Frigid'
          end as ClimateZone

with WHEN indicating each condition and only one ELSE for the "every other case".

 

It looks almost as if you were attempting something like

SELECT CASE 
   when missing(Sales.Program)            THEN "Repo" 
   when Sales.Program in ("Retail","Repo") then Value 
   when Sales.Program in ("Regular Dealer", "Repo") then Value
   when Sales.Program in ("Insurance Recovery", "Repo") then Value
   when Sales.Program in ("Manufacturer/Factory", "Repo") then value 
   ELSE Sales.Program Value AS CALCULATION

BUT the repeated use of "Repo" means that only the first would be considered. Also Else needs to have a value to assign to the variable Calculation

 

You may have to describe in English what you were attempting.

If you wanted to see if SAles.Program was one of a list of values use IN with all of the values inside the ()

Solution
‎10-14-2016 04:02 PM
Grand Advisor
Posts: 17,478

Re: New addition to SAS

It looks like you're trying to implement a CASE statement?

 

Here's the SAS reference, it's typically 

 

CASE

 when <condition> then <value>

when <condition> then <value>

else <value>

END as NEW_VARIABLE_NAME

 

Also, if you want to check for multiple values, ie x = 1 or x=2 or x=3 you can use IN so it becomes

x in (1, 2, 3) and similarily for character variables.

 

http://support.sas.com/documentation/cdl/en/sqlproc/69049/HTML/default/viewer.htm#n0a85s0ijz65irn1h3...

 

I can't quite understand your logic so hopefully this is enough for you to translate this to SAS. 

☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 308 views
  • 1 like
  • 3 in conversation