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.
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.
Why do you have to use a macro to do this?
Because i have a lot of table with datetime instead of date for date parameters.
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?
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...
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.
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.
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, ^=, |, ||, ~=.
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;
Yes but when I use &codeName2="&datedisplay"d, the format datetime22.3 is back.
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".
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
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.
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.