Hello there ! I'm writing a macro that will conditionaly reverse datetimes in char form to datetime in numeric form. For that, i'm using a mix of data steps and sql procs : %macro NUMdatetime(table,var);
proc sql;
select type into :type
from dictionary.columns
where memname='&table.' and libname='SDTM' and name="&var.";
quit;
proc sql;
select label into :label1
from dictionary.columns
where memname='&table.' and libname='SDTM' and name="&var.";
quit;
%if %symexist(type)=1 %then %do;
%if &type.^=char %then %do;
%put ERROR: Variable is not character.. transformation not possible !!;
%goto exit;
%end;
data SDTM.&table.; set SDTM.&table.;
format &var.2 datetime19.;
&var.2=input(&var.,anydtdtm.);
drop &var.;
rename &var.2=&var.;
run;
data SDTM.&table.; set SDTM.&table.;
label &var.="&label1.";
run;
%exit:
%end;
%mend NUMdatetime; My issue is that SQL part doesn't seem to execute correctly. My &type var stays the same as it was before the command. If I execute both parts separatly, the &type variable updates correctly. I have no error nor warnings messages in log. I tried to resolve each one in a different macro, but it looks like the problem comes from SQL code inside a macro : %macro SQL_EVAL(table,var);
proc sql;
select type into :type
from dictionary.columns
where memname='&table.' and libname='SDTM' and name="&var.";
quit;
proc sql;
select label into :label1
from dictionary.columns
where memname='&table.' and libname='SDTM' and name="&var.";
quit;
%mend SQL_EVAL;
%SQL_EVAL(AE,AEENDTC); ---------------------------------------------------------------------------------------------------------------- 13767 %macro SQL_EVAL(table,var); 13768 proc sql; 13769 select type into :type 13770 from dictionary.columns 13771 where memname='&table.' and libname='SDTM' and name="&var."; 13772 quit; 13773 proc sql; 13774 select label into :label1 13775 from dictionary.columns 13776 where memname='&table.' and libname='SDTM' and name="&var."; 13777 quit; 13778 %mend SQL_EVAL; 13779 %SQL_EVAL(AE,AEENDTC); NOTE: No rows were selected. NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.00 seconds NOTE: No rows were selected. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds ---------------------------------------------------------------------------------------------------------------- When I only use the program, without using it in a macro : proc sql;
select type into :type
from dictionary.columns
where memname='AE' and libname='SDTM' and name="AEENDTC";
quit;
proc sql;
select label into :label1
from dictionary.columns
where memname='AE' and libname='SDTM' and name="AEENDTC";
quit;
%put &type.;
%put &label1.; ---------------------------------------------------------------------------------------------------------------- 13781 proc sql; 13782 select type into :type 13783 from dictionary.columns 13784 where memname='AE' and libname='SDTM' and name="AEENDTC"; 13785 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.07 seconds cpu time 0.03 seconds 13786 proc sql; 13787 select label into :label1 13788 from dictionary.columns 13789 where memname='AE' and libname='SDTM' and name="AEENDTC"; 13790 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 13791 %put &type.; num 13792 %put &label1.; End Date/Time of Adverse Event ---------------------------------------------------------------------------------------------------------------- And there is works, the type was numeric at this point. Any ideas ? Thanks for reading !
... View more
