Rename Missing Value

sdang — Wed, 08 Apr 2015 18:39:49 GMT

When I join Table1 and Table2 by Zipcodes, not all of zips in Table1 will be available in Table2. Therefore E.G will show the not-matching zip codes as blank.

Table1	Table2
ZipCodes	ZipCodes
	MarketingZones

So any zips from Table2 does not match with Table1, I will get a blank cell under the column MarketingZones. How do I rename the blank cell as "Other" or "Missing"?

See below for sample output.

Re: Rename Missing Value

CaseySmith — Wed, 08 Apr 2015 20:44:47 GMT

One way to accomplish would be to create a SAS Format to format missing values with labels, then apply that format to the column. For example...

In EG, click Tasks->Data->Create Format to start the Create Format task (or you can just submit the code below, similar to what the task will generate)
Give the format a name (ex. "MissingLabelFmt")
Check Specify format width and give it a reasonable width (ex. 12)
On the Define Formats page, click New
Specify your desired label (ex. "Other" or "Missing")
In the Range definition area, select Missing values in the Values dropdown
Run the task

Note: By default, the Create Format task creates formats in the temporary WORK library. If you want a permanent format, save to a permanent format catalog (specify in the task or code).

(The task will create code similar to the following, which you could just manually submit:

PROC FORMAT LIB=WORK;

VALUE $MissingLabelFmt (DEFAULT=12)

" " = "Other";

RUN;

)

After creating the user-defined format, apply it to your column...

In the EG Query Builder (where you did the join), on the Select Data tab, double-click the MarketingZone variable
Click the Change button next to the Format field
Click the User Defined category and select the format you defined (ex. $MISSINGLABELFMT.)
Click Ok, Ok, then Run

Result:

Note: Using a SAS format only affects how the values are displayed. It does not alter the actual stored value (missing value in this case).

Re: Rename Missing Value

sdang — Wed, 08 Apr 2015 21:20:02 GMT

Thank you

Re: Rename Missing Value

sdang — Wed, 08 Apr 2015 21:31:40 GMT

You solved my problem.

topic Re: Rename Missing Value in SAS Enterprise Guide

Rename Missing Value

Re: Rename Missing Value

Re: Rename Missing Value

Re: Rename Missing Value