While Loop in proc sql

Pradeepbanu — Thu, 22 Mar 2018 10:07:13 GMT

I have metrics sas table like below

work.met_table
Metrics_Id   Metrics_desc
     1       Count_Column
     2       Sum_Column
     3       Eliminate_column

I wanna do something like doing while loop in T-sql

select count(*) :cnt_tbl from work.met_table

%let init_cnt = 1

while (&init_cnt = &cnt_tbl)
begin
   select Metrics_desc into :met_nm
   from work.met_table
   where metrics_id = 1

    Insert into some_sas_table
    Select * from another table where Metrics_desc =&met_nm
       /* Here I wanna loop all values in metrics table one by one */
end
%put &init_cnt = &int_cnt+1;

How this can be done in proc sql? Thanks in advance

Re: While Loop in proc sql

Kurt_Bremser — Thu, 22 Mar 2018 10:33:52 GMT

Write the SQL for this part:

Insert into some_sas_table
Select * from another table where Metrics_desc =&met_nm

Once you get it to run for one metrics_desc, replace the value with a macro variable (&met_nm), and wrap it into a macro definition with met_nm as parameter.

Then run that off your met_table with call execute:

data _null_;
set met_table;
call execute('%yourmac(' !! trim(metrics_desc) !! ');');
run;

topic Re: While Loop in proc sql in SAS Procedures

While Loop in proc sql

Re: While Loop in proc sql