macro isodates error: simple example provided

[saskapa]

Hi,

My macro  convert an input date variable into a IS8601 format.

Can someone explain me why does my macro not work when my input variable has a char type (BIRTHDT_RAW) ? Normally, it should not process BRIDTH_RAW because it is has a type='C' yet it creates an error in the log.

I can’t see the reason …Perhaps the use of vtype ?

option symbolgen mprint ;
%macro isodates(indtc=,
                outdtc=)/ minoperator DES='transform raw dates to iso formats';
 
      if vtype(&indtc)='N' then do;
         if  index(vinformat(&indtc.),'TIME') then do;
         &outdtc=put(&indtc,E8601DT.);
         end;
      end;
      
%mend isodates;

DATA test;
infile cards;
input BIRTHDT_RAW $1-10  @11 BIRTHDT DATETIME22.3;
informat BIRTHDT DATETIME22.3 ;
cards;
12APR1994 12APR1994:00:00:00.000
;
run;

data test1; set test;
%isodates(indtc=BIRTHDT,
          outdtc=BIRTHDTC);
run;




data test1; set test;
%isodates(indtc=BIRTHDT_RAW,
          outdtc=BIRTHDTCR);
run;

Regards,

saskap

[Kurt_Bremser · Accepted solution]

A) you don't need to define a macro (at the moment,see C)), as you do not use %if or %do. Everything you do can be done in open code.

B) you have

&outdtc=put(&indtc,E8601DT.);

which means that, at compile time, you try to force the data step compiler to apply a numerical format to a character variable, which naturally fails. The fact that this branch would not be reached at runtime is irrelevant, as data structures and syntactical validity are checked at compile time.

C) If you want to automate the transformation, you need to expand the macro so that you first determine the types of the named variables (eg from dictionary.columns), and then dynamically create the main data step code according to the data types, using a character or numeric format.

[saskapa]

Thanks KurtBresmer ! 

I wrote a macro because I want to make it generic for  any dataset  in my studies. The input variable can be numeric or have a char type . So what I need to know first is the type and then conditionaly proceed with a statement. 

You say "you try to force the data step compiler to apply a numerical format to a character variable" but this is exactly what I wanted to avoid by writing this condition :   

if vtype(&indtc)='N' then do;

but obviously this conditions only work at execution time...ins't ? 

Cheers

saskap

[Kurt_Bremser]

Then you want to write something like

%macro convert_isodate(libname,inmemname,outmemname,invar,outvar);

proc sql;
select type into :vartype from dictionary.columns where upcase(libname) = upcase("&libname") and upcase(memname) = upcase("&inmemname") and upcase(name) = upcase("&invar");
quit;

data &libname..&outmemname;
set &libname..&inmemname;
%if "&vartype" = "num" %then %do;
&outvar = put(&invar,e8601dt.);
%end;
run;

%mend;

Here you use the macro engine to dynamically create code.

[RW9]

Hi,

What error are you talking about as when I run that code there is no error.  Do you mean this:

NOTE 484-185: Format $E8601DT was not found or could not be loaded.

This due to the compiler looking at your code, seeing that its a character variable, and thus assuming the $e8601dt.  

To be honest though, why are you writing a macro for this at all?  What do you hope to gain from writing this?  Simply put, if you ask a SAS programmer what the input() function does, they would be able to tell you, if you ask them what %isodates() does they wouldn't, it called obfuscation.  All the code given, resolves to this:

data test;
  infile cards;
  input birthdt_raw $1-10  @11 birthdt datetime22.3;
  informat birthdt datetime22.3 ;
cards;
12APR1994 12APR1994:00:00:00.000
;
run;

data test1; 
  set test;
  birthdtcr=input(cats(birthdt_raw,"T00:00:00"),datetime.);
  format birthdtcr birthdt e8601dt.;
run;

Even counting the keystrokes needed for ththat line, there is no saving to be had.

[saskapa]

Hi RW9,

Thanks for your answer. Always clear and helpful. 

I understand your remark about the excessive use of macro.

But how would you do then,  when you are dealing with multiple studies with dozens of datasets with different dates variables. You want somehow to have a generic code that will consider most the scenarios. You will then avoid to type the code again and again.  But I agree with you about obfuscation but there are still macro specification and macro header specs to understand what the macro does.

Cheers,

saskap

[RW9]

Input() function is generic code, your unlikely to write anything as generic, and stable.

For your point I am going to assume that your using CDISC.  It is all to do with your metadata.  The Define.XML should be designed first, plus you wil have your database specifications, import specifications etc.  Once you have everything documented there should be no ambiguity of what a variables type is going into any logic, nor what comes out.  As for not typing code each, this:

birthdtcr=input(cats(birthdt_raw,"T00:00:00"),datetime.);

Is no more time consuming to write each time than:

%isodates(indtc=BIRTHDT,
          outdtc=BIRTHDTC);

 So you don't save anything by using that methdology, all you do is obfuscate the code underlying the logic.  If you goto the level of letting your metadata create your programs for you, then the whole discussion is irrelevant.

[RW9]

Ah, so I see @Kurt_Bremser post.  Are you looking for some generic code to manipulate many datasets/variables?  If so then the keyword here is still metadata.  Kurt has given you some macro code, the other alternative is this:

data abc;
  a="01JAN2014"d; b="Something"; output;
  a="12NOV2015"d; b="Else"; output;
  format a date9.;
run;

data def;
  l="2014-12-24"; fgh="Hello world"; output;
run;

/* This code will process any variable which has date9 format */
data _null_;
  set sashelp.vcolumn (where=(libname="WORK" and format in ("DATE9.")));
  by  memname;
  if first.memname then call execute(cat('data '||catx('.',libname,memname),'; set ',catx('.',libname,memname),';'));
  call execute(cat(strip(name),'_c=put(',strip(name),',date9.);'));
  if last.memname then call execute('run;');
run;

Here we are using the SAS metadata, and from that generating the code necessary to do all variables with date9 format.  To be hnest, I am not sure that is the best approach though.  If you have the specs of the data going in and the specs of the data coming out, then the actual coding is pretty tiny part of it, so saving some typing is not beneficial.

[data_null__]

The behavior of the data step format interpretation is a good thing but is not working for you here.

Format $E8601DT was not found or could not be loaded

Here you need PUTN function

BIRTHDTCR=putn(BIRTHDT_RAW,'E8601DT')

However, you will get a conversion NOTE.  when the variable is character even though the assignment is not executed.

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).

To make this clean you need to determine the type before hand and generate the appropriate code.