Complicated SAS date conversion issue

[monicajane84]

Ok, my ultimate goal is to perform a simple calculation on two dates to get the number of days between the two. This is part of a large program that needs to be automated for a user with minimal SAS knowledge. So they won't be able to troubleshoot date conversion/calculation issues easily.

Problem 1 - Each time the data (in Excel) are imported into SAS, depending on how complete the data is for both date fields, they are importing as either numerical or character.

I attempted to correct for this by using the SQL DICTIONARY table COLUMNS to identify the character dates (if they've been imported as such) and a macro to convert them to a SAS date using:

date_num = input(date_char, mmddyy10.);

The whole thing works and the calculations process correctly. However...

Problem 2 - the new format for date_num still isn't numerical, it's a character SAS date. Weird? This would be fine, as long as the calculations worked out, if I didn't need to use these dates in a PROC PRINT later on. They print as SAS dates (i.e. 17564) and can't be date formatted because they're still character vars!

Any ideas would be much appreciated! Thanks in advance

 

[kdt82]

The first thing I would do, is to check why date_num is being recognised as a character datatype. The most likely culprit is a length statement with $.

You can determine the variable type at run time with the vType function (returns C for character variables, and N for numerics). This should then allow you to specify how you want to handle such data.

For added flexibility, you can use the anydtdte type formats and informats to have SAS determine what date informat should be applied to the data.

*Define that date will be in the format month,day,year;
options datestyle = mdy ;

data _null_ ;
   input date_char :$10. date_num :anydtdte. ;

   if vType(date_char) eq 'C' then date = input(date_char, anydtdte10.);

   put 'date_char=' date ;
   if vType(date_num) eq 'N' then date = date_num ;
   
   put 'num_char=' date ; ;
   format date date9. ;
   cards ;
01-02-1966 01-02-1966
;;;