I am using "options missing =0" in my proc sql to account for blank data in my two Sales columns, however I want my dates to remiain "." or blank. so the date will not be replaced by 01/01/1960.
Is this possible? How could I solve for this?
proc sql;
options missing=0;
create table Test_Sales as
select distinct
a.*,
b.'2022_AEP_Sales'n,
b.'2021_Application Submit Date'n,
c.'2023_AEP_Sales'n,
c.'2022_Application Submit Date'n
from SalesTable3 a
left join Sales2022 b
on a.AGENT_INDIVIDUAL_WRITING_NUMBER=b.AGENT_INDIVIDUAL_WRITING_NUMBER
left join Sales2023 c
on a.AGENT_INDIVIDUAL_WRITING_NUMBER=c.AGENT_INDIVIDUAL_WRITING_NUMBER
group by a.AGENT_INDIVIDUAL_WRITING_NUMBER, b.'2021_Application Submit Date'n
;quit;
Example:
case when c.'2022_Application Submit Date'n>0 then c.'2022_Application Submit Date'n else . end as '2022_Application Submit Date'n
Example:
case when c.'2022_Application Submit Date'n>0 then c.'2022_Application Submit Date'n else . end as '2022_Application Submit Date'n
Applying the MISSING= option does not change the values in your data set. It changes the report only.
If you want to change sales numbers, so that they have a minimum value of 0 when the data does not contain a value, change the SELECT logic. For example:
max(b.'2022_AEP_Sales'n, 0) as b.'2022_AEP_Sales'n,
Most likely, you can then get rid of the MISSING= option. But in no case will you see 01JAN1960 when the actual data value is a missing value.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.