topic Re: Define a filter with variable in data step with do loop- SAS in New SAS User

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

FRAFLUTE — Fri, 01 Feb 2019 09:29:14 GMT

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! :)`

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

RW9 — Fri, 01 Feb 2019 09:56:24 GMT

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;

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

BrunoMueller — Fri, 01 Feb 2019 10:46:05 GMT

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?

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

FRAFLUTE — Fri, 01 Feb 2019 15:46:57 GMT

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

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

FRAFLUTE — Fri, 01 Feb 2019 15:50:26 GMT

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!

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

FRAFLUTE — Fri, 01 Feb 2019 16:59:28 GMT

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.

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

BrunoMueller — Mon, 04 Feb 2019 08:51:07 GMT

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/347689

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

FRAFLUTE — Mon, 04 Feb 2019 23:07:56 GMT

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!

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

RW9 — Tue, 05 Feb 2019 10:28:59 GMT

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.

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

BrunoMueller — Tue, 05 Feb 2019 15:29:28 GMT

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;