BookmarkSubscribeRSS Feed
Nobuko42
Calcite | Level 5

Greetings All,

 

I'm running a query in where one set of data has a list of zip codes for members, and I have another file which has a list of zip codes and their corresponding region. The second file is used to map the regions for each zip code. However the first file has some zip codes that do not have a region for, thus when I run the query, I get the '.' value indicating this.

 

I would like to tell the query that when a match isn't there, instead of giving me '.' , I would rather it fill in the cell with 'NA' instead. I have tried using is null and is missing, but I keep getting a query error, stating that the 'when' statement does not match the requirements.

 

I am using SAS EG 7.1 and OS Windows 7, thank you.

9 REPLIES 9
Reeza
Super User
Create a computed column and use a CASE statement to create the conditional variable.

CASE when not missing(new_value) then new_value
else 'NA'
kannand
Lapis Lazuli | Level 10

Hello,

I noticed that you mentioned "Query".  I am not familiar with EG 7 environment. However, in case of a SQL, I would recommend using COALESCE function that will exactly do what you need.   There should be something similar available on EG 7 or may be the same function is available.... worth looking up... 

 

If you like to see details with examples for COALESCE (), here you go:

 

https://msdn.microsoft.com/en-us/library/ms190349.aspx

 

Hope this helps... Good Luck...!!!

Kannan Deivasigamani
Reeza
Super User

@kannand solution is easier :).

And SAS, SAS SQL supports the coalesce function. Process is the same, create a computed column with

Coalesce(var_value, 'NA') 

Nobuko42
Calcite | Level 5

Thank you all for the responses, the Coalesce function did exactly what I needed but I have one other issue, which is what I think what was given me trouble. The variable is considered a number, thus if I try to change it to 'NA' I get an error saying it doesn't match, I'm assuming I have to change my variable into a string. How would I go about doing that, so that I can use the Coalesce to give out an NA value?

kannand
Lapis Lazuli | Level 10
Hi,
Here is a code that you may try. .... There might be alternatives but this is simple to understand using basic SAS code.... The second record has the missing value next to the date.

data have;infile datalines dlm='09'x;
input F_Date:yymmdd8. Analyst_Code;
datalines;
20010305 5
20011228
20020223 7
20020518 5
20020826 7
20021001 9
;RUN;
DATA HAVE; SET HAVE;
LENGTH ANALYST_CODE_C $5.;
IF analyst_code = '.'
then analyst_code_C = 'NA';
ELSE ANALYST_CODE_C = PUT(ANALYST_CODE,$5.);
run;
proc sql;
select f_date, ANALYST_CODE_C From have;
quit;

Here are the results:

F_Date ANALYST_CODE_C
15039 5
15337 NA
15394 7
15478 5
15578 7
15614 9

Good Luck...!!!
Kannan Deivasigamani
Reeza
Super User
Change your function to COALESCEC

COALESCEC(PUT(VAR_VALUE, 8.), 'NA')
ballardw
Super User

Or create a custom format to display missing numeric as NA (or other text);

Proc format;

value Na;

. = 'NA';

run;

And associate that format with the variable. You don't have to change a data type, if you do calculations on the variable it is still treated as missing, and you need not make another pass through the data at all.

kannand
Lapis Lazuli | Level 10

I've incorporated the format solution by ballardw in the same example I stated earlier.  The format and SQL statement is what  you would need to focus.

data have;infile datalines dlm='09'x;
input F_Date:yymmdd8. Analyst_Code;
datalines;
20010305 5
20011228
20020223 7
20020518 5
20020826 7
20021001 9
;RUN;
Proc format;
value Na
. = 'NA';
run;
proc sql;
select f_date,analyst_code format=Na. from have;
Quit;

 

 

 

Kannan Deivasigamani
Patrick
Opal | Level 21

If using SAS EG and your data is in SAS then you could also create a format with your lookup data (zipcode, region). With EG you can do this in a "point and click" manner using "Tasks/Date/Create format from data set".

 

Define "N/A" for your "other" cases

Capture.PNG

 

Then either create a new variable using "put(zipcode,<format.>)" or simply apply your format directly to the zipcode.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 9507 views
  • 6 likes
  • 5 in conversation