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

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

noahmyers — Wed, 08 Nov 2017 22:51:02 GMT

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.

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.

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

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

Reeza — Wed, 08 Nov 2017 22:52:08 GMT

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;

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

noahmyers — Wed, 08 Nov 2017 22:57:53 GMT

Running the following code

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

Returned

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

Reeza — Wed, 08 Nov 2017 23:00:04 GMT

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);

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

noahmyers — Wed, 08 Nov 2017 23:06:14 GMT

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;

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

ballardw — Wed, 08 Nov 2017 23:12:33 GMT

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;

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

data_null__ — Wed, 08 Nov 2017 23:17:07 GMT

@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.

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

Reeza — Wed, 08 Nov 2017 23:18:24 GMT

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

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

Tom — Wed, 08 Nov 2017 23:36:51 GMT

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.

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

noahmyers — Thu, 09 Nov 2017 00:34:43 GMT

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!

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

Kurt_Bremser — Thu, 09 Nov 2017 11:59:25 GMT

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.