i am trying to add '-'between date9.

[Santt0sh]

Hi Experts,

Iam trying to add '-'hypen between date.

01JAN2000

to

01-JAN-00

Please find the Code  and logs.

Kindly suggest.

%let dsn = sales_region_200003;
%let ndte = %sysfunc(TRANWRD(%sysfunc(putn(%sysfunc(inputn(%scan(&dsn,-1,'_'),yymmn6.)),date9.)),' ','-'));

%put &ndte;



LOG:

 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 68         
 69         %let dsn = sales_region_200003;
 70         %let ndte = %sysfunc(TRANWRD(%sysfunc(putn(%sysfunc(inputn(%scan(&dsn,-1,'_'),yymmn6.)),date9.)),' ','-'));
 71         
 72         %put &ndte;
 01MAR2000
 73         
 74         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 84         

 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 68         
 69         %let dsn = sales_region_200003;
 70         %let ndte = %sysfunc(TRANWRD(%sysfunc(putn(%sysfunc(inputn(%scan(&dsn,-1,'_'),yymmn6.)),date9.)),' ','-'));
 71         
 72         %put &ndte;
 01MAR2000
 73         
 74         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 84         

[ChrisNZ · Accepted solution]

You really should keep the year whole.

%put ndte = %sysfunc(inputn(%scan(&dsn, -1, _), yymmn6.), date11.);

ndte = 01-MAR-2000

Also this question has been asked before.

https://communities.sas.com/t5/SAS-Procedures/Reg-Date-in-DD-MMM-YY/td-p/55232

[Santt0sh]

Hi Chris!

Thank  you for you reply!

I am trying to do a lookup from oracle table and SAS Dataset to check the sales and area, to create a report of sales and salesperson for dates and on oracle tables the dates are -'01-JAN-00' . please suggest if i can use cast to convert the date only for comparison.

[ChrisNZ]

I'm pretty sure Oracle understands 4-digit years '01-JAN-2000' .

[ChrisNZ]

My preference is to code

date '2017-01-21'

to avoid any confusion.

[Santt0sh]

Hi Chris!

I will make a note of this!

Thank you for your help!

[PaigeMiller]

@Santt0sh 

My general rule here is that life is always more difficult when you try to handle dates as characters strings, that must be pulled apart somehow, modified, and then re-assembled. Just be cause you can do it this way does not mean you should do it this way.

Dates are much easier to handle as numeric variables. Not only can you use built in formats such as DATE9. or DATE11. (and many others), but you can create your own if the built in formats don't get the job done. So, look how easy this is using the PROC FORMAT PICTURE statement:

proc format;
    picture datedash low-high ='%0d-%b-%y'(datatype=date);
run;

Now you have a format that has dashes in it and two-digit year that you can apply to any valid numeric SAS date value.

data have;
    date='01JAN2024'd;
    output;
    date='22FEB2024'd;
    output;
    format date datedash.;
run;

Even though you marked an answer as correct above, I hope you and everyone else reading this far will consider this approach, I consider it superior to the method that pulls strings apart, makes changes, and then reassembles the string. Plus, since the variable is numeric, it will sort properly, and you can perform arithmetic or boolean logic on them, which you cannot do with strings representing dates.

[ChrisNZ]

@PaigeMiller You can also build in the single quotes that Oracle requires.

proc format;
  picture dateora low-high ='''%0d-%b-%y''' (datatype=date);
run;

data TEST;
  DATE='01JAN2024'd; 
  putlog DATE= dateora. ;
run;

DATE='01-Jan-24'

[ballardw]

---

@Santt0sh wrote:

Hi Chris!

Thank  you for you reply!

I am trying to do a lookup from oracle table and SAS Dataset to check the sales and area, to create a report of sales and salesperson for dates and on oracle tables the dates are -'01-JAN-00' . please suggest if i can use cast to convert the date only for comparison.

---

How do you connect to Oracle?

I would think that the SAS Access for Oracle would handle the date conversions for you.

[Santt0sh]

Hi Supre_User,

Use Libname or Pass thru to connect, and it did work.

Thank you for you help.