topic Re: Based on value dataset creation ..using macros. in New SAS User

Based on value dataset creation ..using macros.

dhruvakumar — Wed, 19 Jun 2019 06:53:27 GMT

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

Re: Based on value dataset creation ..using macros.

PeterClemmensen — Wed, 19 Jun 2019 06:55:13 GMT

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

Makes it much easier to help you

Re: Based on value dataset creation ..using macros.

dhruvakumar — Wed, 19 Jun 2019 07:32:23 GMT

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

Re: Based on value dataset creation ..using macros.

PeterClemmensen — Wed, 19 Jun 2019 07:41:00 GMT

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;

Re: Based on value dataset creation ..using macros.

Kurt_Bremser — Wed, 19 Jun 2019 07:48:14 GMT

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;

Re: Based on value dataset creation ..using macros.

dhruvakumar — Wed, 19 Jun 2019 07:51:01 GMT

Hi Draycut,

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

Re: Based on value dataset creation ..using macros.

PeterClemmensen — Wed, 19 Jun 2019 07:52:14 GMT

@Reeza wrote this

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

Perhaps that will help 🙂

Re: Based on value dataset creation ..using macros.

Kurt_Bremser — Wed, 19 Jun 2019 08:06:43 GMT

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.

Re: Based on value dataset creation ..using macros.

PeterClemmensen — Wed, 19 Jun 2019 09:01:57 GMT

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

Re: Based on value dataset creation ..using macros.

Kurt_Bremser — Wed, 19 Jun 2019 09:57:27 GMT

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;

Re: Based on value dataset creation ..using macros.

PeterClemmensen — Wed, 19 Jun 2019 10:49:22 GMT

Ah yes, I missread 🙂 Makes sense!

Re: Based on value dataset creation ..using macros.

Reeza — Wed, 19 Jun 2019 15:33:29 GMT

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