Based on value dataset creation ..using macros.

[dhruvakumar]

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...

[PeterClemmensen]

What would each data set contain? Can you provide an example of what your data looks like?

Makes it much easier to help you

[dhruvakumar]

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

[PeterClemmensen]

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;

[dhruvakumar]

Hi Draycut,

Thanks for reply, is there any best way can done by using macros.Thanks!

[PeterClemmensen]

@Reeza wrote this

https://communities.sas.com/t5/SAS-Communities-Library/Splitting-a-SAS-data-set/ta-p/517960

Perhaps that will help 🙂

[Kurt_Bremser]

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.

[Kurt_Bremser]

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;

[PeterClemmensen]

@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? 🙂

[Kurt_Bremser · Accepted solution]

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;

[PeterClemmensen]

Ah yes, I missread 🙂 Makes sense!

[Reeza]

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...

---