PROC SQL within a macro - Failed to execute properly

[Althaea]

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 !

[Kurt_Bremser · Accepted solution]

Macro triggers do not resolve when enclosed in single quotes. Use double quotes instead.

[Tom]

Most likely the issue you are having is that INTO will not create the macro variable if no observations meet the where clause.

Either test the SQLOBS automatic variable and/or set a default value before the select.

proc sql noprint;
%let type=;
   select type into :type
     from dictionary.columns
     where memname='&table.' and libname='SDTM' and name="&var."
   ;
%if &sqlobs %then ....
%if %length(&type) %then ...

Especially if you are trying to find a table with an & in the member name.   

    where memname=%upcase("&table.") and libname='SDTM' and upcase(name)=%upcase("&var.")

[Althaea]

After fixing double quotes the program works fine !

Thanks for your help !