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.
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...
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...
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).
Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF
View now: on-demand content for SAS users
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...
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...
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).
Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF
View now: on-demand content for SAS users
Thank you
You solved my problem.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.