SAS can write to multiple outputs in a single data pass. I tried this, and with the help of AI, I got it working. However, there might be OS-level restrictions on open file handles. Depending on your configuration, a process is usually limited to a certain number of open file handles. If the unique values in your splitting column exceed this limit, you could run into problems. But if you have a limited number of unique values and a large dataset to split, this method should work well for you.
For a production-ready solution, the next steps could include adding parameter error handling, output name length handling and maybe offering different output options for the user (Excel?).
If you want to test custom steps after watching that helpful YouTube video, here are the codes for that:
/*Prompt UI*/
{
"showPageContentOnly": true,
"pages": [
{
"id": "page1",
"type": "page",
"label": "Page 1",
"children": [
{
"id": "DSN",
"type": "inputtable",
"label": "Input DataSet Name",
"required": true,
"placeholder": "",
"visible": ""
},
{
"id": "Column",
"type": "columnselector",
"label": "Split my data with one column",
"include": null,
"order": false,
"columntype": "a",
"max": null,
"min": null,
"visible": "",
"table": "DSN"
},
{
"id": "text1",
"type": "text",
"text": "This is informational text.",
"visible": ""
},
{
"id": "Prefix",
"type": "textarea",
"label": "Prefix for the names of the output datasets",
"placeholder": "",
"required": true,
"visible": ""
}
]
}
],
"values": {
"DSN": {
"library": "sashelp",
"table": "class"
},
"Column": [],
"Prefix": "Split_"
}
}
/*Program*/
/**
* Macro to split a dataset into multiple datasets based on unique values of a specified column.
*
* Parameters:
* - DSN: The name of the input dataset (default is sashelp.class).
* - Column: The column based on which the dataset will be split.
* - Prefix: The prefix for the names of the output datasets.
*
* Usage:
* %split_dataset(DSN=sashelp.class, Column=SEX, Prefix=SEXSplit_);
*/
%macro split_dataset(DSN=sashelp.class, Column=SEX, Prefix=Split_);
/* Get unique values from the specified column */
proc sql noprint;
select distinct &Column into :values separated by ' '
from &DSN;
quit;
/* Use proc contents to get the column type */
proc contents data=&DSN out=colinfo(keep=name type) noprint;
run;
data _null_;
set colinfo;
if upcase(name) = upcase("&Column") then do;
if type = 2 then call symput('coltype', 'char');
else if type = 1 then call symput('coltype', 'num');
end;
run;
/* Loop through each unique value and create a new dataset */
%let i = 1;
%do %while (%scan(&values, &i) ne );
%let value = %scan(&values, &i);
%put Processing value: &value;
%if &coltype = char %then %do;
data &Prefix.&value;
set &DSN;
where &Column = "&value";
run;
%end;
%else %do;
data &Prefix.&value;
set &DSN;
where &Column = &value;
run;
%end;
%let i = %eval(&i + 1);
%end;
%mend split_dataset;
/* Call macro */
%split_dataset(DSN=&DSN, Column=&Column, Prefix=&Prefix);
... View more
