Desktop productivity for business analysts and programmers

Rename Missing Value

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 104
Accepted Solution

Rename Missing Value

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.

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

Capture.PNG


Accepted Solutions
Solution
‎04-08-2015 04:44 PM
SAS Super FREQ
Posts: 271

Re: Rename Missing Value

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:

table.png

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

View solution in original post


All Replies
Solution
‎04-08-2015 04:44 PM
SAS Super FREQ
Posts: 271

Re: Rename Missing Value

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:

table.png

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

Frequent Contributor
Posts: 104

Re: Rename Missing Value

Thank you

Frequent Contributor
Posts: 104

Re: Rename Missing Value

You solved my problem.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 431 views
  • 0 likes
  • 2 in conversation