topic Re: How to change 0 in date to "."? in SAS Programming

How to change 0 in date to "."?

LMSSAS — Wed, 02 Nov 2022 16:58:18 GMT

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;

Re: How to change 0 in date to "."?

PaigeMiller — Wed, 02 Nov 2022 17:04:11 GMT

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

Re: How to change 0 in date to "."?

Astounding — Wed, 02 Nov 2022 17:27:38 GMT

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.