BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PSIOT
SAS Employee

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

16 REPLIES 16
PeterClemmensen
Tourmaline | Level 20

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

PSIOT
SAS Employee

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

PaigeMiller
Diamond | Level 26

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?

--
Paige Miller
PSIOT
SAS Employee

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

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Tom
Super User Tom
Super User

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. 

PSIOT
SAS Employee

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, ^=, |, ||, ~=.

Tom
Super User Tom
Super User

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;

 

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

 

Tom
Super User Tom
Super User

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

PSIOT
SAS Employee

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

Kurt_Bremser
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

 

PSIOT
SAS Employee

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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