DATA Step, Macro, Functions and more

create loop which applies the same calculations over a set of tables

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

create loop which applies the same calculations over a set of tables

Hi all,

 

I have different data tables named

NB_PRODUCT_GROUP1_1, NB_PRODUCT_GROUP1_2,....., NB_PRODUCT_GROUP1_24

 

I need to do the following for each of the above tables located in "C:...\Output_Calculation_Sets\Source"

1. select about 96 columns (TIme, Period length,...,surplus)

2. transpose the data

3. export the data into csv to  the folder "C:...\Output_Calculation_Sets\results"

repeat the above for all 24 tables to have 24 csv named nb_grp1_1.csv, nb_grp1_2.csv, nb_grp1_3.csv,...,nb_grp1_24.csv

 

I'm using the following method, I've added a variable &yer to the source table so that I get a table name with a variable attached to it NB_PRODUCT_GROUP1_&yer. I then run the code below by changing %let yer= 1 to 24 (very manual and time consuming)

Can someone help me to create a code which can loop the above.

 

As bonus, instead of having 24 csv it would help to have all of them in one csv file with tabs nb_grp1_1, nb_grp1_2, nb_grp1_3,...,nb_grp1_24

 

Thanks in advance.

Krish

 

 

LIBNAME Source BASE "C:...\Output_Calculation_Sets\Source";
%let yer= 1;

 

proc sql;
create table work.nb_grp1_&yer
as select

Time,
Period_Length,
DISC_RATE,

.......,
MORT_ASS,

Surplus

FROM Source.NB_PRODUCT_GROUP1_&yer;
QUIT;


proc transpose
DATA = work.nb_grp1_&yer
name=Time let OUT = WORK.InputSetFormatted;
id Time;


proc export
DATA = work.nb_grp1_&yer
dbms=csv
outfile="C:...\Output_Calculation_Sets\results\nb_grp1_&yer..csv"
replace;


Accepted Solutions
Solution
‎10-23-2017 06:05 AM
Super User
Posts: 5,518

Re: create loop which applies the same calculations over a set of tables

Posted in reply to krish_101

Most of what you ask for is relatively easy.  But you are not ready yet.  You need to have a working process for 1 file, and what you have posted is just not working yet   ... missing RUN statements, transposing data with illegal commands, then using the input for the transpose instead of the output.  Once you have a working process, you can automate it in this way:

 

%macro loop;

 

%do yer=1 %to 24

    

    Your working process goes here ...

 

%end;

 

%mend loop;

 

%loop

 

However, if you attempt this before your process is actually working, you are just turning a problem into a larger problem.

View solution in original post


All Replies
Solution
‎10-23-2017 06:05 AM
Super User
Posts: 5,518

Re: create loop which applies the same calculations over a set of tables

Posted in reply to krish_101

Most of what you ask for is relatively easy.  But you are not ready yet.  You need to have a working process for 1 file, and what you have posted is just not working yet   ... missing RUN statements, transposing data with illegal commands, then using the input for the transpose instead of the output.  Once you have a working process, you can automate it in this way:

 

%macro loop;

 

%do yer=1 %to 24

    

    Your working process goes here ...

 

%end;

 

%mend loop;

 

%loop

 

However, if you attempt this before your process is actually working, you are just turning a problem into a larger problem.

PROC Star
Posts: 1,325

Re: create loop which applies the same calculations over a set of tables

Posted in reply to krish_101

Astounding already described a macro loop solution for you, which is what you are asking for, to create a loop...

 

An alternative that avoids the need to loop (and avoids the macro language) would be to concatenate your 24 dataset together, and add a variable for GroupID (1-24).  With that, you shouldn't need a loop.   You just select the variables you want, transpose them with a BY statement for group (I think), and export the data. to CSV.

 

You say you would prefer one CSV with multiple tabs instead of 24 CSVs.  A CSV file doesn't support tabs, because it's just a text file.  With the above dataset you could easily make one CSV file with all the data identified by GroupID.  Or you could use PROC PRINT with a BY statement and ODS EXCEL to make an excel file with one worksheet per by group.

 

I'm a big fan of macro looping.  But there are many situations where macro looping can be replaced with by group processing. 

 

 

New Contributor
Posts: 2

Re: create loop which applies the same calculations over a set of tables

Posted in reply to krish_101
Thank you both for your help.
I've done the loop as explained by Astounding. It works Smiley Happy
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 139 views
  • 3 likes
  • 3 in conversation