Define a filter with variable in data step with do loop- SAS

[FRAFLUTE]

Good Morning, i've this problem.

there are 2 dataset

Dataset "ID Customer" where i have this:

id       |  Customer Name   |
-----------------------------
123456   | Michael One      |
123123   | George Two       |
123789   | James Three      |

and the second dataset named "transaction":

id       |  Transaction | Date
-----------------------------------
123456   | Fuel         | 01NOV2018
123456   | Fuel         | 03NOV2018
123123   | Fuel         | 10NOV2018
123456   | Fuel         | 25NOV2018
123123   | Fuel         | 13NOV2018
123456   | Fuel         | 10DEC2018
123789   | Fuel         | 1NOV2018
123123   | Fuel         | 30NOV2018
123789   | Fuel         | 15DEC2018

the results that i want is to create 3 db like a 3 customer id that i've in the first Dataset named:

_01NOV2018_15NOV_123456_F
_01NOV2018_15NOV_123123_F
_01NOV2018_15NOV_123789_F

that contains:

For  _01NOV2018_15NOV_123456_F :
id       |  Transaction | Date
-----------------------------------
123456   | Fuel         | 01NOV2018
123456   | Fuel         | 03NOV2018

For _01NOV2018_15NOV_123123_F :

id       |  Transaction | Date
-----------------------------------
123123   | Fuel         | 10NOV2018
123123   | Fuel         | 13NOV2018

For _01NOV2018_15NOV_123789_F

empty

I need to create a variable for a clause where in data step... how can i make this?

thanks for help! :)`

[RW9]

Sorry, you will need to be clearer in what you want.  Start by posting test data in the form of a datastep.

Dataset ID Customer is never used in the post?

If you just want first to last then:

data want;
  set have;
  length db $200;
  retain db;
  by id;
  if first.id then db=cats("_",put(date,date9.),"_[PLACEHOLDER]_",put(id,best.),"_F";
  if last.id then do;
    db=tranwrd(db,"[PLACEHOLDER]",put(date,mmyy5.));
    output;
  end;
run;

  

[FRAFLUTE]

Hi RW9, thanks for your support!

Sorry for the imprecision... Datase ID Customer is a read only dataset where i want to read the customer ID that will be a variable for a filter in the second dataset "transaction".

For example: i want that sas read the first customer id (123456) and make a filter in the second dataset (where customer id =123456 and data between 01NOV2018 and 15NOV2018) to create a export file with the name _01NOV2018_15NOV_123456_F.

This filter will have to repeat for all customer id that is present in the first dataset.

I want to produce 1 file for all customer id that is in the first dataset for a data range that  could be the previous month of the estraction.

Thanks a lot!!

[FRAFLUTE]

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      |
run;
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         | 03NOV2018
123123   | Fuel         | 10NOV2018
123456   | Fuel         | 25NOV2018
123123   | Fuel         | 13NOV2018
123456   | Fuel         | 10DEC2018
123789   | Fuel         | 1NOV2018
123123   | Fuel         | 30NOV2018
123789   | Fuel         | 15DEC2018
run;

For simplify your work this is the input code. My process will be able to read all of the customer id in the first data set (customers) and produce a dataset named _01NOV2018_30NOV2018_123456_F (first day previuous month_last day previous month_customer id_F) with all of the transaction for each customer id in the previous month.

[BrunoMueller]

Some questions on the why:

• Do you really want to create a new table for each id you have in the first table?
• How many ids will your first table have, just a few, or several 1000s?
• What are the new tables created for each id used for?

[FRAFLUTE]

Thnks for the interest!

- Yes, i need to create a new table for each id that i have in the first table cause i need to send the estraction to all of these customer

- The id are not a lot. They could be maximun 100 id

- The new table is all the transaction of an id customer in the data range (the data range could be the previous month).

Thank you!

[BrunoMueller]

You might want to use the this pattern https://blogs.sas.com/content/sasdummy/2012/03/20/sas-program-by-processing/ by @ChrisHemedinger , if your second table is not to big, this is a good approach.

Of course you need to adapt it to your needs.

Also have a look at this post as well https://communities.sas.com/t5/SAS-Programming/Splitting-up-dataset-based-on-unique-variable/td-p/34...

[FRAFLUTE]

Great! It work! This is the action that i mean...

I've costructed this:

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

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         | 03NOV2018
123123   | Fuel         | 10NOV2018
123456   | Fuel         | 25NOV2018
123123   | Fuel         | 13NOV2018
123456   | Fuel         | 10DEC2018
123789   | Fuel         | 1NOV2018
123123   | Fuel         | 30NOV2018
123789   | Fuel         | 15DEC2018
run;

 proc sql noprint;
	  select strip(put(count(distinct id),15.)) into :varCount from transactions;
	  select distinct id into :varVal1- :varVal&varCount  from transactions;
	  %let today=%sysfunc(today(),date9.);
	  %let data_i=%sysfunc(intnx(month,"&today"d,-3,b),date9.);
	  %let data_f=%sysfunc(intnx(month,"&today"d,-3,e),date9.);
  quit;
%put &varCount &data_i &data_f;

%macro ReportOnEachType;
  %do index = 1 %to &varCount;
  proc sql;	
		create table pippo as
		select * 
		from transactions
		where id=&&varVal&index and date between intnx('month',today(),-3,'b') and intnx('month',today(),-3,'e');
  quit;
 %end;
%mend;
%ReportOnEachType;

With this i can repeat the actions for all of the record of the customer dataset but i need to insert a variable data_i and data_f in the beetween and i need to give a personalized name to the table (now i called it "PIPPO" and for this in the result i see only the last result with the name PIPPO).

How can i do? 

Thanks a lot!

[RW9]

I am sorry, but this really is not the way to program.  You are effectively re-programming the system, which will result in code which is hugely resource hungry and code which is difficult to maintain.  The proper process would be:

1) Merge the two datasets

2) Assign group values to each observation

3) Program a set of manipulations using by group processing with the group values assigned in 2)

4) Report the data using a by group defined in 2).

There is never a need, nor is it ever a good idea to start putting data into macro, using macro loops etc.  It just multiplies the resouce cost of your code, and makes it unmaintainable.

[BrunoMueller · Accepted solution]

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;