Using Proc SQL within a SAS Macro

#1
Hello,

I am an amateur SAS programmer. I just encountered a problem using the Proc SQL statement within a macro. I am using the Proc SQL to connect to the orcle database and pull out a value from a table. I am posting the code that I have written below. Any help will be greatly appreciated. Thanks in advance!

%macro

fct_calc_agt_vars_prdcr_type (p_prdcr_key=,p_grp_trsln_type_cd=);
%let info_grp_trsln_txt = 'NOT FOUND';
%let prdcr_type_cd = 0;


SUBMIT

"PROC SQL;
connect to odbc as mycon (datasrc=******x user=****** password=******);

select * from connection to mycon (

SELECT DECODE (COUNT (1), 0, 'NOT FOUND', 'FOUND')

INTO :grp_trsln_txt

FROM info_grp_trsln

WHERE init_val_cd = '0' || p_prdcr_key

AND grp_trsln_type_cd = p_grp_trsln_type_cd;

);

disconnect from mycon;

QUIT;"

;
END SUBMIT

%IF &grp_trsln_txt = 'FOUND'
%THEN %DO
SUBMIT

"PROC SQL;
connect to odbc as mycon (datasrc=******x user=****** password=******x);

select * from connection to mycon (

SELECT grp_by_trsln_1_cd

INTO :prdcr_type_cd

FROM grp_trsln

WHERE init_val_cd = '0' || p_prdcr_key

AND grp_trsln_type_cd = p_grp_trsln_type_cd;

);

disconnect from mycon;

QUIT;"

;
END SUBMIT

%END
%END %IF
%let fct_calc_agt_vars_prdcr_type = &prdcr_type_cd;
%mend

fct_calc_agt_vars_prdcr_type;
Data

var_test;
result = %fct_calc_agt_vars_prdcr_type (p_prdcr_key =

'A', p_grp_trsln_type_cd = 'B');
run

;
proc

print data=var_test ;
run

;
 
Last edited:

vinux

Dark Knight
#2
What is the Error. You need to paste the LOG.
If you are not getting the log, debug the macro code.

You can debug the code by giving value to macro variable. like
%let p_prdcr_key =A;
%let p_grp_trsln_type_cd = B;

Then run the macro step by step.