PROC SQL within a macro - Failed to execute properly

Althaea — Wed, 27 Mar 2019 17:22:09 GMT

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 !

Re: PROC SQL within a macro - Failed to execute properly

Kurt_Bremser — Wed, 27 Mar 2019 17:32:26 GMT

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

Re: PROC SQL within a macro - Failed to execute properly

Tom — Wed, 27 Mar 2019 17:59:33 GMT

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

Re: PROC SQL within a macro - Failed to execute properly

Althaea — Thu, 28 Mar 2019 09:23:08 GMT

After fixing double quotes the program works fine !

Thanks for your help !

topic PROC SQL within a macro - Failed to execute properly in SAS Programming

PROC SQL within a macro - Failed to execute properly

Re: PROC SQL within a macro - Failed to execute properly

Re: PROC SQL within a macro - Failed to execute properly

Re: PROC SQL within a macro - Failed to execute properly