Rename Missing Value

[sdang]

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.

[CaseySmith · Accepted solution]

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...

1. 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)
2. Give the format a name (ex. "MissingLabelFmt")
3. Check Specify format width and give it a reasonable width (ex. 12)
4. On the Define Formats page, click New
5. Specify your desired label (ex. "Other" or "Missing")
6. In the Range definition area, select Missing values in the Values dropdown
7. 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...

1. In the EG Query Builder (where you did the join), on the Select Data tab, double-click the MarketingZone variable
2. Click the Change button next to the Format field
3. Click the User Defined category and select the format you defined (ex. $MISSINGLABELFMT.)
4. 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).

[sdang]

Thank you

[sdang]

You solved my problem.