topic Re: create loop which applies the same calculations over a set of tables in SAS Programming

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

krish_101 — Sun, 22 Oct 2017 10:38:02 GMT

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;

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

Astounding — Sun, 22 Oct 2017 11:39:17 GMT

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.

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

Quentin — Sun, 22 Oct 2017 12:07:50 GMT

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.

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

krish_101 — Mon, 23 Oct 2017 10:07:49 GMT

Thank you both for your help.
I've done the loop as explained by Astounding. It works 🙂