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;
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.
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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.