Is there a way in SAS EG to reformat a character date of YYYYMMDD to MM/DD/YYYY in a stored format on the server?

[JSJ]

Hi,

Is there a way to store a format for reformatting a character date of YYYYMMDD to MM/DD/YYYY on the server?

We are continually having to reformat character dates with a calculated field.  I would like to either call a stored format or pass the date variable to a function (not sure this is in EG)

Here is an example of our calculated field

CASE

           WHEN ( t1.GIF_GIFT_DATE_OF_RECORD  = '00000000 '   OR TRIM( t1.GIF_GIFT_DATE_OF_RECORD) = '')  THEN   ' '

         ELSE

SUBSTR (t1.GIF_GIFT_DATE_OF_RECORD,5,2) || '/' || SUBSTR (t1.GIF_GIFT_DATE_OF_RECORD,7,2) || '/' ||

SUBSTR (t1.GIF_GIFT_DATE_OF_RECORD,1,4) 

    END

Thanks!

[Steelers_In_DC]

I have two ways of doing the same thing here.  You can use the current format and wrap a input(put()) around it or do it using the format ( I guessed at this since you didn't add any data ).  One uses $char8. and the other uses $char10.

data have;

input GIF_GIFT_DATE_OF_RECORD$;

cards;

20150630

;

run;

data want;

format date date2 mmddyy10.;

set have;

date = input(put(GIF_GIFT_DATE_OF_RECORD,$char8.),yymmdd8.);

date2 = input(put(SUBSTR (GIF_GIFT_DATE_OF_RECORD,5,2) || '/' || SUBSTR (GIF_GIFT_DATE_OF_RECORD,7,2) || '/' ||SUBSTR (GIF_GIFT_DATE_OF_RECORD,1,4),$char10.),mmddyy10.);

run;

[JSJ]

Thanks so much for your reply.

I am not sure how to incorporate this into SAS EG.  I can create a custom format and insert SAS Code, but I can't get it working yet. Any more hints?

Thanks!

[Steelers_In_DC]

I'm not sure what you have, but wherever you see the code above, replace the first line I'm pasting below with the second line:

SUBSTR (t1.GIF_GIFT_DATE_OF_RECORD,5,2) || '/' || SUBSTR (t1.GIF_GIFT_DATE_OF_RECORD,7,2) || '/' ||SUBSTR (t1.GIF_GIFT_DATE_OF_RECORD,1,4)

input(put(SUBSTR (t1.GIF_GIFT_DATE_OF_RECORD,5,2) || '/' || SUBSTR (t1.GIF_GIFT_DATE_OF_RECORD,7,2) || '/' ||SUBSTR (t1.GIF_GIFT_DATE_OF_RECORD,1,4),$char10.),mmddyy10.)

[JSJ]

Actually, let me restate this.

What I really want to do is pass a date that is in character format YYYYMMDD to a function(?) or subroutine and have it return a reformatted date in MM/DD/YYYY format.

The field name of the date input can vary, but it would be in a consistent format.

And this needs to be accomplished in SAS EG.  Ideally, we could create this once and someway store it on the server so that any project could access it.  It would eliminate us as a group having to create calculated fields --

Any ideas?  Thanks to Mark for the SAS base code. I am not sure where this would go in EG, though.

Thanks!

[obi]

data A;
/* from YYYYMMDD to MM/DD/YYYY createing some data to work with*/
input dday;
cards;
20150702
19550606
19750127
19991231
;
run;

/* The dday var is now a character data type and they are
alocated to the right in your table column (check table A)  - You have to use the SAS char functions trim + left
to remove the empthy spaces before you can use the SAS input function
to transform the char date (dday) into a SAS date informat.
After this you can put the date into any date format  */

data R; set A;
format y MMDDYY10.;
x=input(trim(left(dday)),YYMMDD8.);
y = x;
put y;  /* Display dates in the SAS log */
run;

[gcleggs]

Hello

A possible solution to making whichever method you use to reformat your date variable accessible as a callable routine, would be to save it in a Windows/Unix directory that you allocate to your EG session or in a file accessible by using the filename statement. Then you could %include the code in whichever program when needed. In EG, you can specifically allocate a  fileref to your session. Ex. filename X '/dir/somefile.sas', for a single file or

filename X /'dir' which contains multiple members.   Use %include X for single SAS file code or %include X(membername) for a dir that contains more than one member in specified directory, to be inserted at the desired location.  Every organization has its own rules for what can be pulled into an EG session or not. If you can read data via the INFILE statement which usually requires a fileref/filename allocation, you should be able to get this to work if you desire to go this route. 

[JSJ]

Hi all!  Thanks for your help--

Here is what I ended up using-- in a calculated field.  At least I just have to put in the field one time!    Thanks for your help!

PUT(INPUT(t1.GIF_GIFT_DATE_OF_RECORD,YYMMDD8.),mmddyy10.)

[SASKiwi]

Is there a reason why you are having to store dates as character? If you stored your dates as dates to start with (numeric with date format) then you wouldn't have to do all these translations.

[JSJ]

Not sure why we have some character dates and some Sas dates.  Before my time here!

[SASKiwi · Accepted solution]

In my experience it is usually easier to fix the source of problem rather than to keep applying bandaids, assuming you can get agreement to do so.

[JSJ]

Good advice.  I'll definitely keep that in mind.  Thanks very much!

[Peter_C]

SASkiwi

right as you are,

there are still times when I cannot "fix the source"

There is a solution to this problem - even as it is defined - (although it feels hard to swallow)

We can create a format to do this.

It needs a date range defined and as I have seen formats with a million ranges, it seems almost acceptable to create the format that converts a string date in one standard format to a string date in another.

For example to support a century needs only approx 36555 ranges - double that 2 centuries need less than 100K ranges.

%let y1range = 1890 ; %let y2range = 2130 ;

data cntlinD ;

retain type 'C' fmtname 'DAT1CNV'   ;

*YYYYMMDD to MM/DD/YYYY ;

do  date = mdy( 1,1,&y1Range ) to mdy( 12,31, &y2Range );

     start = put( date, yymmddN8. ) ;

     label = put( date, mmddyy10. ) ;

     output ;

end ;

run ;

proc format cntlin= cntlinD ;

run ;

%put demo date test= 22Jul2015 21050722 becomes %sysfunc( putc( 21050722, $dat1cnv. )) ;

On my little system the result line in the log shows

demo date test= 22Jul2015 21050722 becomes 07/22/2105

The 240 year interval over which this informat supports the string date conversion, required only 88 thousand ranges in the format

It could be extended to create an error report for dates outside this range, but that seemed overkill; (to me)

Hope this provides a simple enough demo of how this format can be created.

To make it generally applicable, see your systems administrator, or have a look at the system option INSERT= with value FMTSEARCH

To apply the new format permanently to a list of variables (&varlist ) in a table (&table)

proc datasets library = %scan(&table,1,.)  nolist  ;

modify %scan(&table,-1,.) ;

format &varlist $dat1cnv. ;

run ;

quit ;

One beauty of this method (a new format applied with proc datasets) is you update only headers and not the stored data.

If you consider implementing this, I would suggest generally using the system option NOFMTERR.

cheers

Peter

Message was edited by: Peter Crawford sorry Defect1: The format ddmmyyN has max width 8 so I have correct that bit of code.