BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
noahmyers
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
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

10 REPLIES 10
Reeza
Super User

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;
noahmyers
Fluorite | Level 6

Running the following code

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

Returned2017-11-08 15_56_11-SAS.png

Reeza
Super User

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);
noahmyers
Fluorite | Level 6

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

Reeza
Super User
proc sql;
	create table PERM.CEOTENURE5 as
		SELECT * 
			  ,CASE 
			  		WHEN missing(dateEndRole) THEN today()
					ELSE DateEndRole
			   END as DateEndRoleF
		FROM PERM.CEOTENURE
		;
	quit;
data_null__
Jade | Level 19

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

 

ballardw
Super User

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;
Tom
Super User Tom
Super User

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.

 

noahmyers
Fluorite | Level 6

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!

Kurt_Bremser
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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