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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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.

View solution in original post

12 REPLIES 12
Steelers_In_DC
Barite | Level 11

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
Quartz | Level 8 JSJ
Quartz | Level 8

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
Barite | Level 11

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
Quartz | Level 8 JSJ
Quartz | Level 8

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
Calcite | Level 5 obi
Calcite | Level 5

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
Calcite | Level 5

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
Quartz | Level 8 JSJ
Quartz | Level 8

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
PROC Star

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
Quartz | Level 8 JSJ
Quartz | Level 8

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

SASKiwi
PROC Star

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
Quartz | Level 8 JSJ
Quartz | Level 8

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

Peter_C
Rhodochrosite | Level 12

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.

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 12 replies
  • 2495 views
  • 6 likes
  • 6 in conversation