topic Re: Operators in sas in SAS Programming

Operators in sas

CathyVI — Mon, 22 Aug 2022 19:25:30 GMT

Hi,

I will like to create a variable Zip_1 where:

1 = Response zip code does NOT correlate to a state zip code

0 = Response zip code correlates to a state zip cod.

I used the following data step;

data a;

set b;

if zipcode in (45666,55756,33546,45645,08083) then Zip_1 =0;

else Zip_1=1;

run;

How do I use the operator (not in) so that I can have Zip_1=1 before Zip_1=0. I guess what am trying to ask is what is the correct operator for (not in) so that I will be able to include all the numbers.

data a;

set b;

if zipcode not in (45666,55756,33546,45645,08083)

then Zip_1 =0;

else Zip_1=1; run;

Re: Operators in sas

Reeza — Mon, 22 Aug 2022 19:27:25 GMT

NOT IN is correct, but I suspect those values are character not numeric so you would need to include them in quotes.

If you're having issues, post your code/log.

Re: Operators in sas

CathyVI — Mon, 22 Aug 2022 19:56:43 GMT

@Reeza Is there a quick way to include quotes in all the numbers without manually doing it one after the other. I have a-lot of zip-codes and its time consuming. Thanks

Re: Operators in sas

PaigeMiller — Mon, 22 Aug 2022 20:17:03 GMT

If the list of zip codes is in a SAS data set, you can create a macro variable that has all the desired quotes and commas very easily.

proc sql noprint;
    select distinct quote(cats(zipcode)) into :zipcodes separated by ',' from have;
quit;

and then use the macro variable &zipcodes as your list of zip codes.

ALTERNATIVE

You can use the editor Find/Replace function, select the desired sequence of code, the find a single comma and replace it with ',' (thats quote-comma-quote), and click on Replace in Selection.

That will leave some cleanup to do manually, the first and last quote will not be present, and line breaks will need to be fixed as well.

Re: Operators in sas

Reeza — Mon, 22 Aug 2022 20:11:53 GMT

Where are these zipcodes stored? If you have them in a data set or table, perhaps a join or format is a better approach than manually typing out a list.

Also, confirm your variable type by looking at the proc contents of your data set.

Re: Operators in sas

CathyVI — Mon, 22 Aug 2022 20:14:25 GMT

@PaigeMillerThank you but I really do not understand how you wanted me to do it. I have initially thought about the find and replace but not sure how it will work. Am finding it hard to follow through with your wording, may be you can help me clarify it better.

This is how my code is setup:

data a;

set b;

if Zipcode NOT IN
(21501,21502,21503,21504,21505
21521,21524,21528,21529,21530
21532,21539,21540,21542,21543
21545,21555,21556,21557,21560
21562,21766,20711,20724,20733
20751,20755,20758,20764,20765
20776,20778,20779,21012,21032) then zip_1=0;

else zip_1=1;run;

Re: Operators in sas

PaigeMiller — Mon, 22 Aug 2022 20:21:48 GMT

Select all the zipcodes and commas

Then Find all the commas, replace with ',' (quote-comma-quote) and then click on Replace in Selected, then do a manual cleanup

Although I think the SQL code I posted is even easier and more fool-proof, if the zip codes are in a SAS data set.

Re: Operators in sas

ballardw — Mon, 22 Aug 2022 21:10:07 GMT

@CathyVI wrote:

@Reeza Is there a quick way to include quotes in all the numbers without manually doing it one after the other. I have a-lot of zip-codes and its time consuming. Thanks

Or if all of your Zip code variable values are 5 digits you could convert that to numeric for the comparison:

if input(zipcode,5.) in (<list written without quotes as numeric values>) then ...;

BTW if the Zipcode variable is character you may have seen a note in the log about "numeric values converted to character" for the comparison. Which would typically fail the comparisons as the default conversion that SAS applied in the background would likely have had 7 leading spaces using a BEST12 conversion and the 5 digits in the character stopped comparing as unequal because the first space didn't match the Zip value.

Another approach if the list of Zips of interest is fairly static would be to create a custom informat with the INPUT function to create the numeric value you want. An example:

proc format;
invalue myziplist
'45666','55756','33546','45645','08083' = 0
other = 1
;

data example;
  input zip $5.;
  zip_1 = input(zip,myziplist.);
datalines;
45666
55756
33546
45645
08083
44444
55555
;

If you happen to have a list of the values you want in a data set then it would likely be very easy to create a CNTLIN data set for Proc Format to use that. Which may be helpful if this list is not static. If the list is "very large", not sure where the boundary may be, the Informat may be quicker than a very large IN comparison as well.

Re: Operators in sas

andreas_lds — Tue, 23 Aug 2022 06:49:03 GMT

Where does the list of valid zip codes come from? From a text file hopefully.

If this is the case:

import that file using a data step,
read the docs about creating a format from a dataset (https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/n1e19y6lrektafn1kj6nbvhus59w.htm)
define an informat to convert valid zip codes to 0 and anything else to 1