topic Re: How to use macro to loop through dataset to output specified record in SAS Programming

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

shirleyk — Tue, 23 Aug 2022 07:39:46 GMT

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;

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

yabwon — Tue, 23 Aug 2022 07:56:28 GMT

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);

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

andreas_lds — Tue, 23 Aug 2022 08:25:52 GMT

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;

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

PeterClemmensen — Tue, 23 Aug 2022 08:47:28 GMT

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;

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

yabwon — Tue, 23 Aug 2022 08:49:55 GMT

How could I forget about hash tables!

B-)

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

shirleyk — Mon, 29 Aug 2022 07:19:22 GMT

Thkx.

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

shirleyk — Mon, 29 Aug 2022 07:20:05 GMT

Thkx

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

shirleyk — Tue, 30 Aug 2022 09:44:43 GMT

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