CODEWITHSUNDEEP
databasepostgresqlpostgis
sbell423
sbell423

Reputation: 113

Convert SQL statement in SAS to POSTGRESQL

How do I get this SQL code that worked in SAS to work in POSTGRESQL. Below is the code that worked in SAS but not in POSTGRESQL.

newname='Other';

if fullstate='Alaska' then equal_area_id='RAINIER';

if fullstate='Hawaii' then equal_area_id='SHASTA';

if fullstate='California' then equal_area_id='SHASTA'; 

if fullstate='California' and county in    (45 21 7 63 91 57 33 11 10143 47 99 77 67 109 9 5 17 61 115 113 55 97 95 3)   then equal_area_id='MENDOCINO';

This below script kind of works in POSTGRESQL but it only labels the new field as "TEMECULA" and not "SHIPROCK"

UPDATE new_counties2

SET newname = CASE     
WHEN fullstate = 'Arizona' THEN 'TEMECULA' 

WHEN fullstate = 'Arizona' AND county IN ('1', '17', '5', '15', '7') THEN 'SHIPROCK' END
WHERE newname IN ('Other')

This is going to be for the whole country so I need to get the formatting straight somehow.

Upvotes: 0

Views: 407

Answers (3)

Mark Clements
Mark Clements

Reputation: 130

We can create some example data:

create table test (fullname varchar(20), county integer);
\copy test from stdin with delimiter ',';
Alaska,1
Arizona,2
California,5
California,6
\.

For the first of two alternative approaches, we could consider nested case statements:

select county, fullname,
   case fullname
     when 'Alaska' then 'RAINIER'
     when 'Arizona' then 'SHASTA'
     when 'California' then case
          when county in (45, 21, 7, 63, 91, 57, 33, 11, 10143, 47, 99, 77, 67, 109, 9, 5, 17, 61, 115, 113, 55, 97, 95, 3) then 'MENDOCINO'
          else 'SHASTA' end
     else ''
     end as equal_area_id
from test 
order by county;

This logic may be easier to follow for some readers. Second, and more closely to the datastep code, we could use plpgsql:

create or replace function equal_area_id(fullname varchar(20), county integer) returns varchar(10)
as $$
begin
    if fullname='Alaska' then return 'ALASKA';
    elsif fullname='Arizona' then return 'SHASTA';
    elsif fullname='California' and county in (45, 21, 7, 63, 91, 57, 33, 11, 10143, 47, 99, 77, 67, 109, 9, 5, 17, 61, 115, 113, 55, 97, 95, 3) then return 'MENDOCINO'; 
    elsif fullname='California' then return 'SHASTA';
    end if;
end;
$$ language plpgsql;

select county, fullname, equal_area_id(fullname,county)
from test
order by county;

This is not as concise as the SAS datastep, but is more modularised. Both approaches give the same output:

 county |  fullname  | equal_area_id 
--------+------------+---------------
      1 | Alaska     | ALASKA
      2 | Arizona    | SHASTA
      5 | California | MENDOCINO
      6 | California | SHASTA
(4 rows)

Upvotes: 1

Reeza
Reeza

Reputation: 21264

You're currently hardcoding the rules into your code. I recommend building a table to hold those values and then use a join instead.

Somehow in your system create a table that maps the State and Counties to a equal_area_id. 

State County Equal_area_id
AZ  1 Temecula
AZ  2 Temecula
AZ  3 Shiprock
NV  1 Vegas
NV  2 Vegas
CA  1 Shasta
CA 45 Mendocino
CA 21 Mendocino

Then use a join in postgresql rather than case statements. This way if you need to make updates, you update your table and your code stays the same.

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

The order of conditions in a case expression is important.

Let's say there are 3 conditions,

case when condition1 then 1
     when condition2 then 2
     when condition3 then 3
end

If condition1 is satisfied for a row, condition2 and condition3 will not be evaluated. Similarly if condition1 fails and condition2 is satisfied, condition3 will not be checked.

To get the desired result, use

CASE
WHEN fullstate = 'Arizona' AND county IN ('1', '17', '5', '15', '7') THEN 'SHIPROCK'
WHEN fullstate = 'Arizona' THEN 'TEMECULA'
END

Upvotes: 3

Related Questions