Thanks, I've included the program block below: data _null_;
input params $32.;
call execute('%nrstr(%ColumnSplit('!!strip(params)!!'))');
datalines;
Region
Sector
;
%MACRO ColumnSplit (params);
/* Sets Parameters - These are:
The table to be split
The location for the output tables to be created
The parameter to split the table by
The columns to be dropped from the output table
A project identifier to add to the start of the resulting table names*/
%let indata=SASUSER.SOURCE;
%let outlib=SASUSER;
%let param=¶ms;
%let dropcolumns = Region Sector CCM Customer Office;
%let tablename=&prefix;
/* Creates a Lookup Table containing all unique values in the selected column */
proc sort
data=&indata (keep=¶m)
out=lookup
nodupkey
;
by ¶m;
run;
/* Modifies the contents of the lookup table so that the values are valid SAS names. This is a necessary step as otherwise the output tables
are built per word within the lookup table, which creates duplicates*/
data lookup;
set lookup;
vname = substr(translate(translate(translate(trim(¶m),'','&'),'_',' '),'_','__'),1,32);
run;
/* Creates the output tables */
data _null_;
call execute ('data ');
do until (eof1);
set lookup end=eof1;
call execute("&outlib..&tablename._" !! trim(vname) !! ' ');
end;
call execute ("; set &indata.;");
do until(eof2);
set lookup end=eof2;
call execute("if ¶m = '" !! trim(¶m) !! "' then output &outlib..&tablename._" !! trim(vname) !! ';');
end;
call execute('drop &dropcolumns; run;');
stop;
run;
%MEND ColumnSplit;
... View more
