I have made some changes to your code, see below.
The number of values from the select distinct does not need to be counted, there is a new syntax with the open end in the INTO.
Showed an example on how to put together the name of the new table, it is now based on start and end date plus the id.
Question why are you not taking the values from the first table (customers), is this one needed at all? What if a customer does not have any transactions?
You said you need to send each table, do you mean by email (SAS can send emails directly), and what is the format?
data customers;
infile cards dlm='|';
attrib
id length=8
name length=$20
;
input id name ;
datalines;
123456 | Michael One
123123 | George Two
123789 | James Three
;
data transactions;
infile cards dlm='|';
attrib
id length=8
transaction length=$10
date length=8 format=date9. informat=date9.
;
input id transaction date;
datalines;
123456 | Fuel | 01NOV2018
123456 | Fuel | 03dec2018
123123 | Fuel | 10dec2018
123456 | Fuel | 25dec2018
123123 | Fuel | 13NOV2018
123456 | Fuel | 10DEC2018
123789 | Fuel | 1NOV2018
123123 | Fuel | 30NOV2018
123789 | Fuel | 15DEC2018
;
proc sql noprint;
select distinct id into :varVal1- from transactions;
%let varCount = &sqlobs;
quit;
%put NOTE: &=varCount;
%let someDate = 05jan2019;
%let prevMonthStart = %sysfunc(intnx(month, %sysevalf("&someDate"d), -1, B), date9.);
%let prevMonthEnd = %sysfunc(intnx(month, %sysevalf("&someDate"d), -1, E), date9.);
%macro ReportOnEachType;
%do index = 1 %to &varCount;
%let id = &&varVal&index;
%put NOTE: processing &id;
proc sql feedback;
create table _&prevMonthStart._&prevMonthEnd._&id._F as
select *
from transactions
where id=&id and date between "&prevMonthStart"d and "&prevMonthEnd"d;
quit;
%end;
%mend;
%ReportOnEachType;
... View more