Hi All,
I have one dataset having different departments like sales,marketing,HR.
By using macros based on departments I need to create datasets
Means based on department sales , I have to create sales dataset and for marketing I need to create marketings...for sales one sale dataaset...
For instance the above we have 3 different departments .. It should create 3 different datasets..for n no.of departments ..it should create n no.of datasets
Please could help on this requirement...
My data _null_ will perform one iteration. In the first do loop, it supplies the data step names to the data statement. Then it starts a select() block, and in the second loop it creates the body of the select. Then it ends the select and the data step, and terminates.
The created code looks like this:
data
dept1
dept2
dept3
;
set employee;
select(department);
when ("dept1") output dept1;
when ("dept2") output dept2;
when ("dept3") output dept3;
end;
run;
What would each data set contain? Can you provide an example of what your data looks like?
Makes it much easier to help you
DATASET EMPloyee having :
Name Age Department
John 39 Sales
Mary 29 Marketing
Sam 32 HR
Rob 61 Sales
Toni 23 Marketing
Georg 39 HR
The desired output should be
Data set should be created under name "sales"
Name Age Department
John 39 Sales
Rob 61 Sales
Data set should be created under name "Marketing"
Name Age Department
Mary 29 Marketing
Toni 23 Marketing
and
Data set should be created under name "HR"
Name Age Department
Sam 32 HR
Georg 39 HR
Ok. Use a dynamic approach like this
data have;
input Name $ Age Department $20.;
datalines;
John 39 Sales
Mary 29 Marketing
Sam 32 HR
Rob 61 Sales
Toni 23 Marketing
Georg 39 HR
;
proc sort data=have;
by Department;
run;
data _null_;
if _n_=1 then do;
if 0 then set have;
declare hash h(dataset:"have(obs=0)", multidata:'y');
h.definekey('Department');
h.definedata(all:'Y');
h.definedone();
end;
do until(last.Department);
set have;
by Department;
h.add();
end;
h.output(dataset:Department);
h.clear();
run;
Hi Draycut,
Thanks for reply, is there any best way can done by using macros.Thanks!
@Reeza wrote this
https://communities.sas.com/t5/SAS-Communities-Library/Splitting-a-SAS-data-set/ta-p/517960
Perhaps that will help 🙂
Using a macro just for the sake of using a macro is not efficient; it often leads to convoluted and unnecessarily complicated code.
Note that both @PeterClemmensen 's and my solution require only one pass through the source dataset in the split step.
proc sort
data=employee (keep=department)
out=control
nodupkey
;
run;
data _null_;
call execute('data');
do until (eof1);
set control end=eof1;
call execute(' ' || department);
end;
call execute('; set employee; select(department);');
do until (eof2);
set control end=eof2;
call execute('when ("' || strip(department) || '") output ' || strip(department) || ';');
end;
call execute('end; run;');
run;
@Kurt_Bremser, not entirely sure here. But doesn't your code pass through the source data set 3 times? To my limited knowledge of Call Execute logic, you create three data steps that each execute after your 'data _null_' data step. And each of these data steps read the source data set once? 🙂
My data _null_ will perform one iteration. In the first do loop, it supplies the data step names to the data statement. Then it starts a select() block, and in the second loop it creates the body of the select. Then it ends the select and the data step, and terminates.
The created code looks like this:
data
dept1
dept2
dept3
;
set employee;
select(department);
when ("dept1") output dept1;
when ("dept2") output dept2;
when ("dept3") output dept3;
end;
run;
Ah yes, I missread 🙂 Makes sense!
Why?
You should BY statements to calculate independent summaries instead. Here's an example to calculate the mean of age (you didn't provide salary in your data set) by department into a new data set.
data have;
input Name $ Age Department $20.;
datalines;
John 39 Sales
Mary 29 Marketing
Sam 32 HR
Rob 61 Sales
Toni 23 Marketing
Georg 39 HR
;
proc means data=have NWAY NOPRINT;
class department;
var age;
output out=dept_means mean=avg_age;
run;
proc print data=dept_means noobs label;
run;
@dhruvakumar wrote:
Hi All,
I have one dataset having different departments like sales,marketing,HR.
By using macros based on departments I need to create datasets
Means based on department sales , I have to create sales dataset and for marketing I need to create marketings...for sales one sale dataaset...For instance the above we have 3 different departments .. It should create 3 different datasets..for n no.of departments ..it should create n no.of datasets
Please could help on this requirement...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.