The following code imports a csv file and selects the unique values for each element of VList into macrovariables Var1, Var2 etc. Then it creates a new table for each unique combination of the values in Var1 Var2 from the original CSV file.
I've written the code so it works with 2 elements in VList. But i need it to work for a varying number of elements.
Right now i have 2 distinct macros to loop through each of the elements of Var1 and Var2 (Loop_A, Loop_B), and the macro Combo to create the new table from the distinct combinations. If the number of elements in VList varies, then i would need a varying number of Loop_X's, and Combo would need a varying number of arguments.
Any suggestions on how to accomplish this?
%LET InFile=C:\FilePath\Filename.CSV;
%LET VList=Variable1|Variable2;
%LET VNum=%SYSFUNC(CountW(&VList.));
%PUT VNUM=&VNum.;
proc import datafile="&Infile."
DBMS=csv out=CSV_IN replace;
guessingrows=1000;
run;
%MACRO MVars(VarList);
%DO K=1 %TO &VNum.;
%GLOBAL V&K.;
*creates V1 as first variable in the VList, V2 as the 2nd...etc;
%LET V&K.= %SYSFUNC(SCAN(&VarList.,&K.,|));
%PUT V&K.= &&V&K.;
*Sorts CSV File by V#;
proc sort data=CSV_IN;
by &&V&K.;
run;
*creates a table the same name as the variable, and populates it with the first obs of each distinct input for that variable;
data &&V&K.;
set CSV_IN;
by &&V&K;
if first.&&V&K. then
output;
run;
%GLOBAL Var&K.;
*creates macrovariables Var1, Var2 etc. as the distinct values for V1, V2...;
proc sql noprint;
select &&V&K. into :Var&K.
separated by '|'
from &&V&K.;
quit;
%PUT Var&K. = &&Var&K.;
%END;
%MEND MVars;
%MVars(&VList.);
*Creates MacroVariables Length_1, Length_2 etc, as the number of items in each var#.;
%MACRO LengthOfVars();
%DO L = 1 %TO &VNum.;
%GLOBAL Length_&L.;
%LET Length_&L. = %SYSFUNC(CountW(&&Var&L.,|));
%PUT Length_&L. = &&Length_&L.;
%END;
%MEND LengthOfVars;
%LengthofVars();
*Loop_A and Loop_B cycle through every combination of the distinct values for each var#;
%MACRO LOOP_A();
%DO A = 1 %TO &Length_1.;
%LET AA = %SCAN(&Var1.,&A.,|);
%PUT AA=&AA.;
%LOOP_B;
%END;
%MEND LOOP_A;
%MACRO LOOP_B();
%DO B = 1 %TO &Length_2.;
%LET BB = %SCAN(&Var2.,&B.,|);
%PUT BB=&BB.;
%COMBO(&AA.,&BB.);
%END;
%MEND LOOP_B;
*create a new table for each unique combination of variable values;
%MACRO Combo(FirstVar, SecondVar);
Proc sql noprint;
create table NewTable_&FirstVar._&SecondVar.
as select * from CSV_IN
where &V1.="&FirstVar." and &V2.=&SecondVar.;
quit;
%PUT &V1. &FirstVar. &V2. &SecondVar.;
%MEND COMBO;
%LOOP_A;
