loop the code based on the tab name

radha009 — Thu, 06 Sep 2018 02:32:47 GMT

i have an excel with change in no. of tabs every month. i have written a code for each tab as below. I want to loop the code for each tab and create a new table.

if i have 3 tabs i am importing 3 times with sheet name change & out file name change. ( can we loop this ?????)

PROC IMPORT OUT=file.orders
DATAFILE="&filepath"
DBMS=XLSX;
Sheet="orders" /* changing every month the name*/
RUN;

Also, i have 3 times this code based on the data and create new output table. ( i want to loop import and code and create a table for each of it)

proc sql;
alter table file.customer
add Order_ID char(20) format = $20.;
update file.customer as A
set Order_ID = (select b.Cust_ID from file.Orders as b
where A.Cust_ID=b.Cust_ID);
quit;

data Patch.Order_final;
length Order_1 $ 4;
set file.customer;

if (Order_ID=Cust_ID) then
order_1="Yes";
else
order_1="No";
run;

please help me how can i reduce manual updates and loop the code based on number of tabs.

Re: loop the code based on the tab name

Reeza — Thu, 06 Sep 2018 02:46:50 GMT

Wrap it in macros and call as needed. It also depends on how much of the code is repeatable.

Here's a tutorial on converting a working program into a macro. You should always start with a full working program.

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

In addition, I highly recommend the SAS Macro Appendix Library, where you'll find several examples of looping macro variables and calling it multiple times.

If you need further help, please post specific questions with code and an explanation of what's going wrong.

@radha009 wrote:

i have an excel with change in no. of tabs every month. i have written a code for each tab as below. I want to loop the code for each tab and create a new table.

if i have 3 tabs i am importing 3 times with sheet name change & out file name change. ( can we loop this ?????)

PROC IMPORT OUT=file.orders
DATAFILE="&filepath"
DBMS=XLSX;
Sheet="orders" /* changing every month the name*/
RUN;

Also, i have 3 times this code based on the data and create new output table. ( i want to loop import and code and create a table for each of it)

proc sql;
alter table file.customer
add Order_ID char(20) format = $20.;
update file.customer as A
set Order_ID = (select b.Cust_ID from file.Orders as b
where A.Cust_ID=b.Cust_ID);
quit;

data Patch.Order_final;
length Order_1 $ 4;
set file.customer;

if (Order_ID=Cust_ID) then
order_1="Yes";
else
order_1="No";
run;

please help me how can i reduce manual updates and loop the code based on number of tabs.

topic loop the code based on the tab name in SAS Programming

loop the code based on the tab name

Re: loop the code based on the tab name