CODEWITHSUNDEEP
sql-server-2008
user2448666
user2448666

Reputation: 427

creating dummy variables for zip code and populating existing table

I have a too many zip codes in my data to run an analysis using this as a categorical variable, so I need to create a dummy variable. location, and group zip codes. Currently I have a blank column labeled Location that needs to be populated. Example Follows

ID ZIP location

1 28201

2 28202

3 28203

4 28206

5 28207

6 38300

7 38305

8 38306

I want to do something like an IF THEN, though I am thinking of the IF THEN that is used in SAS. Something like IF 28200< zip <29200 then Location = 1; If 38000< zip < 39000 then location= 2

ID ZIP location

1 28201 1

2 28202 1

3 28203 1

4 28206 1

5 28207 1

6 38300 2

7 38305 2

8 38306 2

Upvotes: 2

Views: 2274

Answers (1)

mdoyle
mdoyle

Reputation: 727

I believe you are looking for CASE. Something like

UPDATE table
SET location = 
    CASE
        WHEN zip > 28200 AND zip < 29200 THEN 1
        WHEN zip > 38000 AND zip < 39000 THEN 2
    END;

Upvotes: 1

Related Questions