How to use macro to loop through dataset to output specified record

[shirleyk]

I have a data called cust_sales and I would like to use macro to loop through customer unique name to filter out the record and save it as another SAS dataset. I've tried below codes and it doesn't work. Can someone help. Thanks.

Desired output from below is to have 4 SAS output (Customer_Anthony, Customer_Samuel, Customer_Rebecca and Customer_Jessica). Customer_Anthony sas output will show ony 2 records, samuel will have 2 records and so on..

cust_sales dataset:

/* Get unique name from current mth */
proc SQL;
CREATE TABLE work.unique_name_list as
SELECT DISTINCT name from work.cust_sales;
QUIT;

/*Loop through unique name list to output each customer name record */

%macro run_loops;
%local cnt, i, cust_name;

/* count unique number of cust_name */
%let dsid = %sysfunc(open(work.unique_name_list));
%let cnt = %sysfunc(attrn(&dsid, nlobs));
%let rc = %sysfunc(close(&dsid));

%do i = 1 %to &cnt;
data _null_;
p = &i,;
set work.unique_name_list point=p;
call symputx('cust_name', name);

/*Filter out data based on unique customer name*/
data Customer_&cust_name.;
set work.cust_sales;
WHERE name = &cust_name.;
run;
stop;
run;
%end;
%mend;

%run_loops;

[yabwon]

Try this:

data have;
input name $ q;
cards;
A 1
B 2
C 3
A 4
B 5
C 6
;
run;
proc print data have;
run;

%macro loopOver(ds,name);
%local i N;
proc sql noprint;
  select distinct UPCASE(&name.)
  into :name1-
  from &ds.
  ;
  %let N = &sqlobs.;
quit;

data
%do i = 1 %to &N.;
 customer_&&name&i  
%end;
;
set &ds.;

select;
  %do i = 1 %to &N.;
   when (UPCASE(&name.)="&&name&i") output customer_&&name&i;
  %end;
  otherwise put "ERROR: !";
end;

run;

%mend loopOver;

%loopOver(have,name);

[shirleyk]

Thkx.

[shirleyk]

if i wanted to export the output to xlsx to my C:\temp, how do I do that?

[andreas_lds]

Not need for macro code.

Unfortunately you have not posted data in usable form, so sashelp.class is used to create one dataset per age.

proc sort data=sashelp.class(keep= Age) out=work.ages nodupkey;
   by Age;
run;

data _null_;
   set work.Ages;
   
   call execute(cats('data work.Age', Age, ';'));
   call execute('set sashelp.class;');
   call execute(cats('where Age=', Age, ';'));
   call execute('run;');
run;

[PeterClemmensen · Accepted solution]

data have;
input name $ q;
cards;
A 1
B 2
C 3
A 4
B 5
C 6
;

proc sort data = have;
   by name;
run;

data _null_;
   if _n_ = 1 then do;
      dcl hash h (multidata:'Y');
      h.defineKey ('_n_');
      h.defineData ('name', 'q');
      h.defineDone ();
   end;
   do until (last.name);
      set have;
      by name;
      h.add();
   end;
   h.output (dataset: catx ('_', 'Customer', name));
   h.clear();
run;

[yabwon]

How could I forget about hash tables! 

B-)

[shirleyk]

Thkx