BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
LMSSAS
Quartz | Level 8

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? 

LMSSAS_0-1667408101174.png

 

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;
1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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
--
Paige Miller

View solution in original post

2 REPLIES 2
PaigeMiller
Diamond | Level 26

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
--
Paige Miller
Astounding
PROC Star

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.

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 943 views
  • 1 like
  • 3 in conversation