Help using Base SAS procedures

While Loop in proc sql

Reply
Occasional Contributor
Posts: 13

While Loop in proc sql

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

Super User
Posts: 10,282

Re: While Loop in proc sql

Posted in reply to Pradeepbanu

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Ask a Question
Discussion stats
  • 1 reply
  • 113 views
  • 0 likes
  • 2 in conversation