topic Re: i am trying to add '-'between date9. in SAS Programming

i am trying to add '-'between date9.

Santt0sh — Tue, 27 Aug 2024 03:24:36 GMT

Hi Experts,

Iam trying to add '-'hypen between date.

01JAN2000

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

Re: i am trying to add '-'between date9.

ChrisNZ — Tue, 27 Aug 2024 04:08:18 GMT

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

Re: i am trying to add '-'between date9.

Santt0sh — Tue, 27 Aug 2024 05:23:46 GMT

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.

Re: i am trying to add '-'between date9.

ChrisNZ — Tue, 27 Aug 2024 05:26:01 GMT

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

Re: i am trying to add '-'between date9.

ChrisNZ — Tue, 27 Aug 2024 05:41:39 GMT

My preference is to code

date '2017-01-21'

to avoid any confusion.

Re: i am trying to add '-'between date9.

Santt0sh — Tue, 27 Aug 2024 13:40:35 GMT

Hi Chris!

I will make a note of this!

Thank you for your help!

Re: i am trying to add '-'between date9.

PaigeMiller — Tue, 27 Aug 2024 17:53:25 GMT

@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.

Re: i am trying to add '-'between date9.

ballardw — Tue, 27 Aug 2024 17:45:16 GMT

@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.

Re: i am trying to add '-'between date9.

Santt0sh — Tue, 27 Aug 2024 21:09:43 GMT

Hi Supre_User,

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

Thank you for you help.

Re: i am trying to add '-'between date9.

ChrisNZ — Tue, 27 Aug 2024 22:32:56 GMT

@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'