Dataset contains a character value in a date column and I can't manipulate it

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Dataset contains a character value in a date column and I can't manipulate it

[ Edited ]

I have a dataset that contains a start date and an end date. The end date column occasionally contains a "C" character, which supposedly stands for "current." I simply need to manipulate this value and change it to a date far in the future or even today's date. The format of the column is YYMMDDN8 Numeric. See screenshot below. 

 

2017-11-08 15_38_17-SAS.png

 

I am quite familiar with SQL, although outside of the context of SAS, and this seems to be a pretty simple solution using SQL code. I have tried the following statement shown in the log below. 

 

2017-11-08 15_39_19-SAS.png

 

However, as you can see from the error above, SAS does not want to compare the numeric type of the column with the 'C' character value, even though this column clearly contains that value. I have attempted changing the format of the column with PUT(), but I was still not able to get a case statement to work as formatted above. 

 

Any help is appreciated.

 

Environment: SAS 9.4 for Windows


Accepted Solutions
Solution
‎11-09-2017 11:58 AM
Super User
Posts: 20,252

Re: Dataset contains a character value in a date column and I can't manipulate it

Posted in reply to noahmyers
proc sql;
	create table PERM.CEOTENURE5 as
		SELECT * 
			  ,CASE 
			  		WHEN missing(dateEndRole) THEN today()
					ELSE DateEndRole
			   END as DateEndRoleF
		FROM PERM.CEOTENURE
		;
	quit;

View solution in original post


All Replies
Super User
Posts: 20,252

Re: Dataset contains a character value in a date column and I can't manipulate it

Posted in reply to noahmyers

Strip the formats and print a small sample to see what the underlying value is:

 

proc print data=dataname (obs=100);
var dateEndRole;
format dateEndRole;
run;
New Contributor
Posts: 4

Re: Dataset contains a character value in a date column and I can't manipulate it

Running the following code

proc print data=PERM.CEOtenure;
var dateEndRole;
format dateEndRole;
run;

Returned2017-11-08 15_56_11-SAS.png

Super User
Posts: 20,252

Re: Dataset contains a character value in a date column and I can't manipulate it

Posted in reply to noahmyers

The only other thing I can think of, is that they're missing, with a missing show as C for some reason?

 

Try the missing function as your filter?

 

where missing(dateEndRole);
New Contributor
Posts: 4

Re: Dataset contains a character value in a date column and I can't manipulate it

It looks like you are correct. Running this code successfully returns only 'C' values. 

proc print data=PERM.CEOtenure;
var dateEndRole;
format dateEndRole;
where missing(dateEndRole);
run;

So in SQL, does this count as a NULL value? I still can't manipulate the data as I am used to. Where am I going wrong?

 

proc sql;
	create table PERM.CEOTENURE5 as
		SELECT * 
			  ,CASE DateEndRole
			  		WHEN NULL THEN today()
					ELSE DateEndRole
			   END as DateEndRoleF
		FROM PERM.CEOTENURE
		;
	quit;

2017-11-08 16_05_43-SAS.png

Solution
‎11-09-2017 11:58 AM
Super User
Posts: 20,252

Re: Dataset contains a character value in a date column and I can't manipulate it

Posted in reply to noahmyers
proc sql;
	create table PERM.CEOTENURE5 as
		SELECT * 
			  ,CASE 
			  		WHEN missing(dateEndRole) THEN today()
					ELSE DateEndRole
			   END as DateEndRoleF
		FROM PERM.CEOTENURE
		;
	quit;
Respected Advisor
Posts: 3,805

Re: Dataset contains a character value in a date column and I can't manipulate it


Reeza wrote:

The only other thing I can think of, is that they're missing, with a missing show as C for some reason?

 

Try the missing function as your filter?

 

where missing(dateEndRole);

 

Perhaps the missing option is set;

 

options missing='C';

 

or more likely it is special missing value .C

 

Probably to indicate continuing.

 

Super User
Posts: 11,578

Re: Dataset contains a character value in a date column and I can't manipulate it

Posted in reply to noahmyers

It appears possible that your data has been read in such a way the actual value is a special missing. If your raw data when read contains .C  (please notice the . before the C) this could be the cause.

If that is the actual case you should be able to use

 

if dateendrole= .C then dateendrole = today();

 

Please see this brief example of a similar data.

data junk;
   input x;
datalines;
1
3
.C
4
;
run;

Proc print data=junk;
run;

data newjunk;
   set junk;
   if x=.C then x=-99;
run;
proc print data=newjunk;
run;
Super User
Super User
Posts: 7,164

Re: Dataset contains a character value in a date column and I can't manipulate it

Posted in reply to noahmyers

The value is the special missing .C. You can test for .C or using the MISSING() function to find those values. You can replace them using IF/THEN logic or just use the COALESCE() function.

data perm.ceotenure2 ;
  set perm.ceotenure;
  dateendrolef = coalesce(dateendrole,today());
  format dateendrolef yymmdd10. ;
run;

If you create a new variable the make sure to attach a date format to it so that it prints in human readable form.  If you want to print in YMD format then YYMMDD10. is more legible or humans than YYMMDD8. since without the hyphens it looks like a number.

 

New Contributor
Posts: 4

Re: Dataset contains a character value in a date column and I can't manipulate it

I ended up using coalesce in the original data pull and everything worked beautifully. 

 

coalesce(A.DateEndRole, today()) as DateEndRole format=yymmddn8.

Thank you Rezza and Tom for your help!

Super User
Posts: 8,080

Re: Dataset contains a character value in a date column and I can't manipulate it

[ Edited ]
Posted in reply to noahmyers

It's probably the setting of your missing= option. Try this for reference:

options missing=C;
data test;
format mydate yymmddn8.;
mydate = today();
output;
mydate = .;
output;
run;

 and then change the value of the option.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

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

Discussion stats
  • 10 replies
  • 115 views
  • 5 likes
  • 6 in conversation