topic Re: Do loop inside Macro not working in SAS Programming

Do loop inside Macro not working

arunrami — Wed, 31 Oct 2018 11:49:25 GMT

Hello All,

Below is the part of the code which I use to create different data set based on the macro variable 'cnt_crr_saa_limit' value using Do loop but I am getting below error messages. Can any one help me out?

/* Do process only when some records available in cr_saa_rating_benchmrk */
%if &cnt_crr_saa_limit. ne 0 %then
	%Do;
		%do i=1 %to &cnt_crr_saa_limit;

			data grp_bbb_below_&i;
				set work.cr_saa_rating_benchmrk
					(keep=portfolio_name bbb_below_benchmark bbb_below_leeway bbb_below_current_level total_exposure
					rename=(bbb_below_benchmark=benchmark bbb_below_leeway=leeway bbb_below_current_level=current_level)
					firstobs=&i obs=&i);
				length rating $ 50;
				rating = 'BBB & Below';
			run;


			data grp_bb_below_&i;
				set work.cr_saa_rating_benchmrk
					(keep=portfolio_name bb_below_benchmark bb_below_leeway bb_below_current_level total_exposure
					rename=(bb_below_benchmark=benchmark bb_below_leeway=leeway bb_below_current_level=current_level)
					firstobs=&i obs=&i);
				length rating $ 50;
				rating = 'BB & Below';
			run;

			data grp_nr_&i;
				set work.cr_saa_rating_benchmrk
					(keep=portfolio_name nr_benchmark nr_leeway nr_current_level total_exposure
					rename=(nr_benchmark=benchmark nr_leeway=leeway nr_current_level=current_level)
					firstobs=&i obs=&i);
				length rating $ 50;
				rating = 'Not Rated';
			run;

		%end;

ERROR;

74         %if &cnt_crr_saa_limit. ne 0 %then
SYMBOLGEN: Macro variable CNT_CRR_SAA_LIMIT resolves to        3
75            %Do;
76                %do i=1 %to &cnt_crr_saa_limit;
                 _
                 180
SYMBOLGEN: Macro variable CNT_CRR_SAA_LIMIT resolves to        3

ERROR 180-322: Statement is not valid or it is used out of proper order.

Re: Do loop inside Macro not working

Kurt_Bremser — Wed, 31 Oct 2018 12:01:19 GMT

Iterative %do is not allowed in open code, it can only be used in a defined macro.

Re: Do loop inside Macro not working

andreas_lds — Wed, 31 Oct 2018 12:03:23 GMT

At least an %end statement is missing to close

%if &cnt_crr_saa_limit. ne 0 %then

The following simplification runs without errors. So the problem must be before the code posted.

%macro box;
   %if &cnt_crr_saa_limit. ne 0 %then %Do;

      %do i=1 %to &cnt_crr_saa_limit;
         %put &=i;
      %end;
   %end;
%mend;

%let cnt_crr_saa_limit =        3;
options symbolgen mprint mlogic;

%box;

Re: Do loop inside Macro not working

MSMM — Wed, 31 Oct 2018 12:22:15 GMT

you should use it inside the macro body in addition to every %if statement should have close %end for example

%macro test;

%if &cnt_crr_saa_limit. ne 0 %then    
%Do;      
  Your CODE          
%end;

%mend test ;

%test;

Re: Do loop inside Macro not working

arunrami — Wed, 31 Oct 2018 12:37:05 GMT

Here is the actual code, I have declared macro

%macro Info;
proc sql noprint;
		*connect to oracle (user=&ora_user. password="&ora_pw." path=&ora_path.);
		insert into work.cr_saa_rating_benchmrk 
			/*		select * from connection to oracle (*/
		select * from work.A;

		/*	DISCONNECT FROM oracle;*/
	quit;

	/*proc print data=work.cr_saa_rating_benchmrk;*/
	proc sql noprint;
		select count(*) into :cnt_crr_saa_limit from work.cr_saa_rating_benchmrk;
	quit;

	%put #### number of records read from REP_SAA_LIMIT#### &cnt_crr_saa_limit.;

	/* Do process only when some records available in cr_saa_rating_benchmrk */
	%if &cnt_crr_saa_limit. ne 0 %then
		%do;
			%do i=1 %to &cnt_crr_saa_limit;

				data grp_bbb_below_&i;
					set work.cr_saa_rating_benchmrk
						(keep=portfolio_name bbb_below_benchmark bbb_below_leeway bbb_below_current_level total_exposure
						rename=(bbb_below_benchmark=benchmark bbb_below_leeway=leeway bbb_below_current_level=current_level)
						firstobs=&i obs=&i);
					length rating $ 50;
					rating = 'BBB & Below';
				run;

				data grp_bb_below_&i;
					set work.cr_saa_rating_benchmrk
						(keep=portfolio_name bb_below_benchmark bb_below_leeway bb_below_current_level total_exposure
						rename=(bb_below_benchmark=benchmark bb_below_leeway=leeway bb_below_current_level=current_level)
						firstobs=&i obs=&i);
					length rating $ 50;
					rating = 'BB & Below';
				run;

				data grp_nr_&i;
					set work.cr_saa_rating_benchmrk
						(keep=portfolio_name nr_benchmark nr_leeway nr_current_level total_exposure
						rename=(nr_benchmark=benchmark nr_leeway=leeway nr_current_level=current_level)
						firstobs=&i obs=&i);
					length rating $ 50;
					rating = 'Not Rated';
				run;

		%end;
%end;
%mend;
%info;

Re: Do loop inside Macro not working

Kurt_Bremser — Wed, 31 Oct 2018 12:43:53 GMT

When I run that code as posted, I only get ERRORs from the Base SAS code because of the missing libs/datasets, but NO problem at all with the macro code.

Please post the log from this code as is.

@arunrami wrote:

Here is the actual code, I have declared macro

%macro Info;
proc sql noprint;
		*connect to oracle (user=&ora_user. password="&ora_pw." path=&ora_path.);
		insert into work.cr_saa_rating_benchmrk 
			/*		select * from connection to oracle (*/
		select * from work.A;

		/*	DISCONNECT FROM oracle;*/
	quit;

	/*proc print data=work.cr_saa_rating_benchmrk;*/
	proc sql noprint;
		select count(*) into :cnt_crr_saa_limit from work.cr_saa_rating_benchmrk;
	quit;

	%put #### number of records read from REP_SAA_LIMIT#### &cnt_crr_saa_limit.;

	/* Do process only when some records available in cr_saa_rating_benchmrk */
	%if &cnt_crr_saa_limit. ne 0 %then
		%do;
			%do i=1 %to &cnt_crr_saa_limit;

				data grp_bbb_below_&i;
					set work.cr_saa_rating_benchmrk
						(keep=portfolio_name bbb_below_benchmark bbb_below_leeway bbb_below_current_level total_exposure
						rename=(bbb_below_benchmark=benchmark bbb_below_leeway=leeway bbb_below_current_level=current_level)
						firstobs=&i obs=&i);
					length rating $ 50;
					rating = 'BBB & Below';
				run;

				data grp_bb_below_&i;
					set work.cr_saa_rating_benchmrk
						(keep=portfolio_name bb_below_benchmark bb_below_leeway bb_below_current_level total_exposure
						rename=(bb_below_benchmark=benchmark bb_below_leeway=leeway bb_below_current_level=current_level)
						firstobs=&i obs=&i);
					length rating $ 50;
					rating = 'BB & Below';
				run;

				data grp_nr_&i;
					set work.cr_saa_rating_benchmrk
						(keep=portfolio_name nr_benchmark nr_leeway nr_current_level total_exposure
						rename=(nr_benchmark=benchmark nr_leeway=leeway nr_current_level=current_level)
						firstobs=&i obs=&i);
					length rating $ 50;
					rating = 'Not Rated';
				run;

		%end;
%end;
%mend;
%info;

Re: Do loop inside Macro not working

arunrami — Wed, 31 Oct 2018 12:53:37 GMT

Hi Brem, You are right. I was incorrectly using data step with dataline statment , thats what the cause of an error. Now working fine.

Thanks!! 🙂