How to convert datatime to a date format (date9.) in SAS macro

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

How to convert datatime to a date format (date9.) in SAS macro

Dear all,

I try to convert a variable with format datetime22.3 in variable with format date9. using SAS macro.

With my code  variable stays in format datetime.

 

My code:  

format &codename2 date9.;
&codeName2=%sysfunc(datepart(&dateDisplay));

 

the value in dateDisplay is nyumeric.

 

Thanks a lot for your help.


Accepted Solutions
Solution
‎04-28-2017 11:22 AM
Super User
Super User
Posts: 6,842

Re: How to convert datatime to a date format (date9.) in SAS macro

[ Edited ]

Why would you be reading values from data into macro variables using %SYSFUNC(GETVARN()) ? 

That sounds crazy.

But if you did do that and you wanted the value that is stored into the macro variable to be formatted as DATE9 style then you could use the DTDATE9 format to generate string that looks like 01JAN2013 from a value of 1672617600.

%put %sysfunc(putn(1672617600,dtdate9));

So you could change your line of code to add the format to the %SYSFUNC() function call.

 

%let dateDisplay=%sysfunc(
  getvarn(&tabid
         ,%sysfunc(varnum(&tabid,%sysfunc(varname(&tabid,&j+1))))
         )
 ,dtdate9
);

Which you should be able to simplify to

%let dateDisplay=%sysfunc(getvarn(&tabid,&j+1),dtdate9);

Since I don't see the value in taking the variable number converting it to the name and then converting back to the variable number. 

View solution in original post


All Replies
PROC Star
Posts: 653

Re: How to convert datatime to a date format (date9.) in SAS macro

Why do you have to use a macro to do this?

Contributor
Posts: 26

Re: How to convert datatime to a date format (date9.) in SAS macro

Because i have a lot of table with datetime instead of date for date parameters.

Trusted Advisor
Posts: 1,783

Re: How to convert datatime to a date format (date9.) in SAS macro

Please give us a few more details. Give us examples of the value of one of the variables in &dateDisplay.

 

What do you mean by "With my code variable stays in format datetime"? Specifically, what variable are you talking about here?

Contributor
Posts: 26

Re: How to convert datatime to a date format (date9.) in SAS macro

Ok, I have tables with some dates in datetime format as like this:01JAN2013:00:00:00.000.

My goal is to create a new column (named with new code) in format date9. for having in the end (01JAN2013).

As i explained before a lot of date without time are in datetime format.

My macro (with table to change as argument) uses a list of dates that i need to change.

I retrieve the date value like this :

%let dateDisplay=%sysfunc(getvarn(&tabid,%sysfunc(varnum(&tabid,%sysfunc(varname(&tabid,&j+1))))));

the value is 1672617600.

In the end I would like to have a new table with dates in format date9.

I don't know if my explanations are clear...

 

Super User
Super User
Posts: 7,691

Re: How to convert datatime to a date format (date9.) in SAS macro

Sorry, no that it is not clear at all.  Please follow the post guidance below the post button - post test data, in the form of a datastep, and what you want the output to look like.  Also provide the logic between the two.  We cannot even tell from the post what you are working with, are the variables date, or date time (numeric format)?  Or are they text, which would account for having mixed.  

Don't overcomplicat yourself by starting by doing lots of macro processing, that never helps.  Start by doing your logic on one dataset, post that and ask then question, then we can look at doing the same thing over several datasets.

Solution
‎04-28-2017 11:22 AM
Super User
Super User
Posts: 6,842

Re: How to convert datatime to a date format (date9.) in SAS macro

[ Edited ]

Why would you be reading values from data into macro variables using %SYSFUNC(GETVARN()) ? 

That sounds crazy.

But if you did do that and you wanted the value that is stored into the macro variable to be formatted as DATE9 style then you could use the DTDATE9 format to generate string that looks like 01JAN2013 from a value of 1672617600.

%put %sysfunc(putn(1672617600,dtdate9));

So you could change your line of code to add the format to the %SYSFUNC() function call.

 

%let dateDisplay=%sysfunc(
  getvarn(&tabid
         ,%sysfunc(varnum(&tabid,%sysfunc(varname(&tabid,&j+1))))
         )
 ,dtdate9
);

Which you should be able to simplify to

%let dateDisplay=%sysfunc(getvarn(&tabid,&j+1),dtdate9);

Since I don't see the value in taking the variable number converting it to the name and then converting back to the variable number. 

Contributor
Posts: 26

Re: How to convert datatime to a date format (date9.) in SAS macro

Thanks for your help,

Now i have retrieved a value in date9.

Except that when i want to put this value in table  like this:


data derivedData;
set derivedData;
format &codename2 Date9.;
&codeName2=&dateDisplay;
run;

the message error is:


MPRINT(DERIVEDTABLE): data derivedData;
MPRINT(DERIVEDTABLE): set derivedData;
MPRINT(DERIVEDTABLE): format OTH_DYSFUN_STARTDATE_NUM Date9.;
NOTE: Line generated by the macro variable "DATEDISPLAY".
24 01JAN2013
_______
22
MPRINT(DERIVEDTABLE): OTH_DYSFUN_STARTDATE_NUM=01JAN2013;
MPRINT(DERIVEDTABLE): run;

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT, IN,
LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, ^=, |, ||, ~=.

Super User
Super User
Posts: 6,842

Re: How to convert datatime to a date format (date9.) in SAS macro

[ Edited ]

Of course it generates an error. It is exactly as if you had written this program. 

data derivedData;
  set derivedData;
  format OTH_DYSFUN_STARTDATE_NUM Date9.;
  OTH_DYSFUN_STARTDATE_NUM=01JAN2013;
run;

It generates an error since 01JAN2013 is not a variable name, a number or a string.

 

If you want to code a date literal you need to quote the value and add D after the quotes. 

  OTH_DYSFUN_STARTDATE_NUM="01JAN2013"d;

So your macro code should look like: 

  &codeName2="&datedisplay"d;

But I don't understand why you are jumping through hoops of taking a value out of a dataset just so you can then put it back into the dataset.  That does not make any sense.  If the value is coming from a variable in the same data set then just use the value and skip the macro variable.

  OTH_DYSFUN_STARTDATE_NUM=datepart(SOMEVAR);

So you could eliminate datedisplay and getvarn() and your macro logic would look more like

  &codeName2=datepart(&codeName1);

And if the value is coming from some OTHER dataset that only has a single DateTime value then just use a SET statement to read it in and skip all of the macro varaibles..

data derivedData;
  set derivedData;
  if _n_=1 then do;
    set otherData(keep=OTH_DYSFUN_STARTDATE_NUM);
    OTH_DYSFUN_STARTDATE_NUM = datepart(OTH_DYSFUN_STARTDATE_NUM);
  end;
  format OTH_DYSFUN_STARTDATE_NUM Date9.;
run;

 

Contributor
Posts: 26

Re: How to convert datatime to a date format (date9.) in SAS macro

Yes but when I use   &codeName2="&datedisplay"d, the format datetime22.3 is back.

 

Super User
Super User
Posts: 6,842

Re: How to convert datatime to a date format (date9.) in SAS macro

The FORMAT attached to a variable is part of the metadata of the variable.

FORMATs can be set by FORMAT statement, but they can also be set because they were attached to the variable in the dataset you are reading. 

 

If you set/merge mutliple input datasets the first non-null format that SAS sees is used. If you use a FORMAT statement then it overrides what was set from reading the metadata of the input dateset. But if you issue multiple FORMAT statements for the same variable in a data step then the last one "wins".  

Contributor
Posts: 26

Re: How to convert datatime to a date format (date9.) in SAS macro

After reading all comment and feeling my code was too complicated, i have simplified but i'm still deceived by my results:

Using now this code:


data derivedData;
set derivedData;
length charDate $ 9;

if input(&varname2,5.)>0 then charDate=put(&varname2-21916,DATE9.);
if input(&varname2,5.)=. then charDate=' ';
newDate=input(charDate,DATE9.);
format newDate DATE9.;
run;

 

CharDate is correct : 23AUG2016 but newDATE is 23AUG2016:00:00:00.000

Why newDate is in datetime22.3?

Thank you

Super User
Posts: 7,399

Re: How to convert datatime to a date format (date9.) in SAS macro

Know your Data (Maxim 3)!

 

So post your data from dataset derivedData (in a data step, as described here).

Without knowing your data, it is impossible to test the code and help you.

Also post the log from your code.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,691

Re: How to convert datatime to a date format (date9.) in SAS macro

Does newdate appear in the dataset deriveddata before this datastep?  Reason - this works fine:

data deriveddata;
  length chardate $9;
  chardate='23AUG2013';
  newdate=input(chardate,date9.);
  format newdate date9.;
run;

 

Simply the whole thing.  Follow my post above - start by posting test data, in the form of a datastep of what your data looks like, then post what the output should look like.  This way we can then see what you have and what you want.  Currently we are just guessing at the fragments of what you post.  Guidance can be found underneath the Post button to the above effect. 

 

Contributor
Posts: 26

Re: How to convert datatime to a date format (date9.) in SAS macro

Ok I found the reason.

Indeed when you stay in SAS environment, the code is working and you have the date in DATE9. format.

But when you export the table in SQL DB, the format DATE9. is not recognized and SQL change in Datetime22.3.

Thank you all for your support

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 16 replies
  • 282 views
  • 0 likes
  • 6 in conversation