date format yyyymmnn in DI

[niespolo]

Hi,

I would write a date in the format yyyymmdd into a .csv file. SAS DI define the Datetime20. Format and Informat by default. I'm able to write the date in the file, but only using the default format. The date field is calculated by using the expression:

COALESCE(DATA_SCADENZA_FINALE, DATA_SCADENZA_INIZIALE )

I would convert the result of this expression in a yyyymmdd format and then write it in the file.

In attach there is the mapping of the File Transfer transformation: the date field calculated by the expression is DATA_INSOLUTO_RID

Thanks in advance.

[Shmuel]

It seems that DATA_INSOLUTO_RID  is already a sas date field and not a datetime field.

The format shown is yymmdd.

If you need use datepart() function to calculate date from a datetime, like:

   DATA_INSOLUTO_RID  = datepart(DATA_INSOLUTO_RID );

   format DATA_INSOLUTO_RID  yymmdd. or yymmn. - as preffered

[niespolo]

Hi Shmuel,

when yymmdd. is used as Format and Informat (as showed in attached), the output in the file is ********

But I don't mind about the field type in the file, that could be a sas Date, a datetime or other. But what I want is that a date in the format yyyymmdd should be printed in the file.

Unfortunatly, datepart doesn't change the behaviour.

Thank you.

[Shmuel]

Thats easy:

DATA_INSOLUTO_RID  = datepart(DATA_INSOLUTO_RID );

   format DATA_INSOLUTO_RID  yymmddn8.;

[niespolo]

Maybe you mean this:

DATA_INSOLUTO_RID  = datepart(DATA_SCADENZA_FINALE);

Infact, the code:

DATA_INSOLUTO_RID  = datepart(DATA_INSOLUTO_RID );

returns the error "ERROR: Variable DATA_INSOLUTO_RID is not on file WORK.W1AE6YSK." and the expression cannot be validated.

But using the setting as in the attached file, returns the following error: " The informat YYMMDDN was not found or could not be loaded"

As showed in the combobox, only yymmdd. or yymmn. could be used; yymmddn8. is not included in the combobox values.

I've tried also yymmdd. in Format and Informats. In this case, DATA_INSOLUTO_RID is printed as 16-11-14 in the file, but I expect 20161114 instead.

Thank you,

[Shmuel · Accepted solution]

I copied a typo from your posts:

The variable is:

  DATA_INSOLUTORID = COALESCE(DATA_SCADENZA_FINALE, DATA_SCADENZA_INIZIALE );

(DATA_INSOLUTORID   instead  DATA_INSOLUTO_RID )

then code should be:

DATA_INSOLUTORID  = datepart(DATA_INSOLUTORID );

   format DATA_INSOLUTORID  yymmddn8.;

[niespolo]

Hi,

I've tried to type yymmddn8. in Format and Informat fields, but SAS returns "The informat YYMMDDN was not found or could not be loaded" when I run the job. I suppose only yymmdd. or yymmn. can be selected from the combobox, as you can see in my last attached file 😞

[Shmuel]

Leave the format as yymmddn8.  (yymmdd8. will result in yy/mm/dd format. try it )

The informat should be yymmdd8.  - SAS will analyze input and convert it correctly to sas date.

Anyhow, why do you need to define informat after converting the variable to be sas date ?!

You need informat only while reading text file (like csv, or .txt ) and that must fit the input date format.

Using sas datasets, you don't need the informat any more.

[niespolo]

yymmddn8. is not allowed in the Format field of the interface of the GUI! Ok, I could not use Informat anyway, but the value in the file shoud be yyyymmdd and not yy/mm/dd (ex. 20061117 and not 16/11/17).

I've tried with yymmdd. in the Format field and 8 in the Length field but it's printed 16-11-17 in the file. 

I've not found how to define the correct formatting of the date in the Format field of the GUI. Could I use an expression instead?

Thank you.

[Shmuel]

SAS date field comtains the number of dates since Jan 1st, 1960.

Just run next code to check different formats:

data _NULL_;
   date = today();
   put date=;  /* as if format 6. */
   put 'Formated - 1  ' date= ddmmyy10.;
   put 'Formated - 2  ' date= ddmmyy8.;
   put 'Formated - 3  ' date= ddmmyyn8.;   put 'Formated - 4  ' date= date9.;   put 'Formated - 5  ' date= date7.;
   /* .. you may try more formats ... */
run;

So SAS date is just a number.  The format defines only how to display it on output,

where output can be a table viewer or a report or when external file like .csv or .txt

If you can, in the Gui, define format as ddmmyy10. or mmddyy10., depends on your habit.

While generating a report or an external file - add your preffered format, as in:

proc ... (any procedure creating output) ..;

...

format date ddmmyyn8.;  /* enter the date variable(s) name to write out */

run;

or even in a data step:

     data out;

       set input;

             put date=  yymmddn8. ;   /* put a row to log */

            file XXX ...;                          /* or file print; */

            put  @5 "Date is "  date yymmdd10. ;  /* put a row to file XXX  or to listing */

     run;

[niespolo]

Hi,

I've solved assigning DATEPART(DATA_SCADENZA_FINALE) to DATA_INSOLUTORID.

In the GUI, DATA_INSOLUTORID, Type = Numeric, Length = 8, Format = yymmddn8. and Informat = None

Thanks everyone.