topic Re: Exporting by group automatically. in SAS Programming

Exporting by group automatically.

Stret — Tue, 04 Aug 2020 09:03:42 GMT

Hello there. I'm looking for a little help with exporting some data.

I have a data set that contains all records where a variable named "IEE" matches to a top ten count which will change every time its ran. The variable is numeric and always 4 numbers.

Sample Is

ID IEE Var1 Var2

111 3007 Red Tree

222 3007 Red House

333 3007 Blue Car

444 4333 Red Car

555 4333 Red Tree

666 4333 Red Tree

777 5544 Red House

888 5544 Red House

What i'm trying to achieve is I want the code to automatically create a separate dataset for each of the values in variable IEE so while i have a top 10 if we were to run it on the above sample we would have 3 datasets.

one for 3007 containing 3 observations

one for 4333 containing 3 observations

one for 5544 containing 2 observations

I'm not too fussed what the output datasets are called as long as they're identifiable (probably sensible to name them the IEE value)

I'm pretty sure this needs to go in a macro but i cant quite figure it.

Any help greatly appreciated

Re: Exporting by group automatically.

Kurt_Bremser — Tue, 04 Aug 2020 09:19:44 GMT

Splitting datasets is, in most cases, not needed and only causes extra work. You can extract a subset for a given analysis by using a WHERE condition, and you can use BY group processing to repeat a certain analysis fior groups in one step.

If, on the other hand, you need to export data to a different environment and, say, for different people, you can also use BY group processing to automatically create a separate file for each group.

So the question is: what do you really want to do with those subsets?

Re: Exporting by group automatically.

Stret — Tue, 04 Aug 2020 09:32:22 GMT

Hello Kurt.

Thank you for responding.

Because I always have a "top 10" ive just created this

data work.Split_&Pos_Entry;
set work.Top10_trans_&Pos_Entry;
by IEE;
retain ID 0;
if first.IEE then ID = ID + 1;
run;

this has given me a data set with an ID 1 to 10 thus grouping each of the IEE's I "think" im ont he right track here.

what i now need to happen is

if id = 1 then output to DATAset_1
if id = 2 then output to DATAset_2

%macro IEEFraud(Pos_Entry= );

Here's my full current code if this helps me explain better

proc sql outobs=10;

create table Top10_&Pos_Entry as
select Distinct MERCHANT_CATEGORY_XCD,
TRANSACTN_POSTING_ETRY_XFLG,
count(MERCHANT_CATEGORY_XCD) as Count,
sum(TRANSACTION_AMT) as Sum_Total

from Data.GET_TRANSACTIONS

Where TRANSACTN_POSTING_ETRY_XFLG = "&Pos_Entry"
and TRANSACTION_FRAUD_TYPE_CD = "CONFIRMED_FRAUD"

Group by 1
Order By Sum_Total DESC

;
quit;

proc sql;

create table Top10_Trans_&Pos_Entry as
select

Auth.FI_TRANSACTION_ID,
Auth.PAN,
Auth.TRANSACTION_dttm,
Auth.MERCHANT_CATEGORY_XCD as IEE,
Auth.TRANSACTION_AMT,
Auth.TRANSACTN_POSTING_ETRY_XFLG,
Auth.AUTH_SECONDARY_VERIFY_XCD as Secure,
Auth.Decision_XCD as Host_Decision,
Auth.MODEL_SCR,
Auth.TRANSACTION_FRAUD_TYPE_CD,

case when
TRANSACTION_FRAUD_TYPE_CD = "CONFIRMED_FRAUD" then 1
else 0
end as Fraud_Flag

from Data.GET_TRANSACTIONS as auth
Inner join work.Top10_&Pos_Entry as IEE on auth.MERCHANT_CATEGORY_XCD = IEE.MERCHANT_CATEGORY_XCD and auth.TRANSACTN_POSTING_ETRY_XFLG = IEE.TRANSACTN_POSTING_ETRY_XFLG
order by IEE;
;
quit;

data work.Split_&Pos_Entry;
set work.Top10_trans_&Pos_Entry;
by IEE;
retain ID 0;
if first.IEE then ID = ID + 1;
run;

%mend IEEFraud;

%IEEFraud(Pos_Entry= E)
%IEEFraud(Pos_Entry= K)
%IEEFraud(Pos_Entry= S)
%IEEFraud(Pos_Entry= F)
%IEEFraud(Pos_Entry= V)

Re: Exporting by group automatically.

Kurt_Bremser — Tue, 04 Aug 2020 09:52:50 GMT

And what will you do with the end results of this?

Re: Exporting by group automatically.

s_lassen — Tue, 04 Aug 2020 15:39:25 GMT

While I think @Kurt_Bremser is mostly right when he states that splitting datasets is often a waste of time, sometimes you still have to. For instance if you are mailing or otherwise forwarding data to specific departments (or customers), and they are not supposed to get the data for the other departments.

A simple solution for creating multiple datasets goes something like this:

1. Create a solution that works for a single value of the parameter, e.g.

data want3007;
  set have;
  where IEE=3007;
run;

2. Make that into a macro, using the parameter:

%macro extract(IEE);
  data want&IEE;
    set have;
    where IEE=&IEE;
  run;
%mend;

3. Test the macro with a single, known parameter:

options mprint; /* lets you see the code generated in the log */
%extract(3007);

4. Use SQL to put all the macro calls into a single macro variable:

proc sql noprint;
  select distinct cats('%extract(',IEE,')') into :doit separated by ';'
  from have;
quit;

5. Execute that:

&doit;