topic Re: Newbie Question - How to do a loop in SAS combine with proc sql? in SAS Programming

Newbie Question - How to do a loop in SAS combine with proc sql?

irisG — Tue, 30 Oct 2018 08:48:36 GMT

Hi I want to create a loop for the following code so I can output a range of names...

data _null_;

do i = 1 to 2;

if i = 1 then class = "bank";

if i = 2 then class = "bonds";

end;

summary = cats(class,"_summary");

call symputx('summary',summary);

Current = cats(class,"_curr");

call symputx('current',current);

run;

%put &summary.;

%put &current.;

But right now my output looks like this

bond_summary

bond_curr

What I dont understand is why

1) my first i =1 clas = bank didnt appear?

2) why is an "s" missing in my "bond"

And then if the above code is run successfully I want to incorporate the following proc sql code but I dont know where to stick the code in the loop.

proc sql;

create table &summary. as

select Market_value

from &current.;

quit;

Many thanks for helping!

Re: Newbie Question - How to do a loop in SAS combine with proc sql?

singhsahab — Tue, 30 Oct 2018 10:02:03 GMT

Hello,

1. To see i can class variable output, you have to add put statement.

2. As per loop , when i=1 then SAS automatically assign length as per first declaration . To avoid truncation you have to use LENGTH statement .

data _null_;
length class $5.;
	do i=1 to 2;

		if i=1 then
			class="bank";

		if i=2 then
			class="bonds";
          put i= class=;
	end;
	summary=cats(class, "_summary");
	call symputx('summary', summary);
	Current=cats(class, "_curr");
	call symputx('current', current);
run;

%put &summary.;
%put &current.;

Re: Newbie Question - How to do a loop in SAS combine with proc sql?

MichaelLarsen — Tue, 30 Oct 2018 10:25:15 GMT

There are many ways to solve this type of problem, one way is to generate code and then execute the generated code.

Example:

/* Generate code */
filename pgm temp;
data _null_;
  file pgm;
  length class summary current $32;
  do i = 1 to 2;
    if i = 1 then class = "bank";
    else if i = 2 then class = "bonds";
    summary = catt(class,'_summary');
    current = catt(class,'_curr');
    put 'proc sql noprint;'
      / '  create table ' summary 'as'
      / '    select Market_Value'
      / '    from ' current ';'
      / 'quit;'
      ;
end;
run;
/* Excute the generated code */
%inc pgm  / source2;
filename pgm clear;

Then code get written to a temporary file in the data step and then included using the %inc statement.

The / Source2 option shows the included code in the log.

Re: Newbie Question - How to do a loop in SAS combine with proc sql?

irisG — Tue, 30 Oct 2018 11:43:23 GMT

Great! Works like a charm! Many thanks!

Re: Newbie Question - How to do a loop in SAS combine with proc sql?

s_lassen — Tue, 30 Oct 2018 12:21:32 GMT

If it works like a charm, you should mark it as the solution!