Split dataset using Macro

[AshokD]

Hi All,

I'm trying to split dataset using product id , but seems it's not working. Can someone have a look and correct me please?

data sample;
input custid prdid prdname $30.;
cards;
222 10 aaaa
333 20 bbbb
444 10 aaaa
444 20 bbbb
555 30 cccc
;
run;

data _null_;

set sample end=last; 

If last then call symputx("n",_n_);

run;

%PUT  &n.;

%macro split(dsn=);

proc sql;
select prdid into :xx from &dsn.;
run;

%do i = 1 %to &n;

data &dsn.&i.;
set &dsn.;
where prdid = &xx.;
run;

%end;

%mend split;
%split(dsn=sample);

[ballardw]

Why?

What can you do with multiple data sets that you can't do with one?

Typically "splitting" data this way adds more complication than any benefit and often means that some one does not understand what is possible with SAS BY group processing.

[AshokD]

Hi,

I've business requirement to split multiple datasets based on the product codes which is input to another team, so thought of using macro to split datasets but seems not working. Can u please correct me with the code?

[Reeza]

You don't show where/how you create the macro variable &xx? Where is that happening?
where prdid = &xx.;

Other methods are illustrated here:
https://blogs.sas.com/content/sasdummy/2015/01/26/how-to-split-one-data-set-into-many/

[Kurt_Bremser]

Do those other teams actually get SAS datasets, or do they need the data in some other form?

[AshokD]

Hi,

Yeah, they need SAS datasets.

[ballardw]

---

@AshokD wrote:

Hi,

Yeah, they need SAS datasets.

---

Unless there are supposed to be restrictions of data for security or similar issues I would suggest placing the "big" data set in a shared storage location and let the users select data based on a Where statement as needed.

If your data changes frequently then you can cause availability due to timing of code execution issues with this split data set approach.

[Tom · Accepted solution]

Your logic is a little confused. You don't care how large the original dataset is, just how many datasets you need to create.

%macro split(dsn=);
  %if not %symexist(nds) %then %global nds;
  %local xx i;

proc sql noprint;
select prdid into :xx separated by ' ' from &dsn.;
%let nds=&sqlobs;
run;

%do i = 1 %to &nds;
data &dsn.&i.;
  set &dsn.;
  where prdid = %scan(&xx.,&i,%str( ));
run;
%end;
%mend split;
%split(dsn=sample);

If your PRDID variable is character (why would you use a numeric variable for an ID?) then add quotes into the macro variable.

select quote(trim(prdid),"'") into :xx separated by ' ' from &dsn.;

Note unless your source dataset is indexed on the split variable it is probably faster to make them all in the same data step.

data
%do i = 1 %to &nds;
  &dsn.&i.
%end;
;
  set &dsn.;
%do i = 1 %to &nds;
  if prdid = %scan(&xx.,&i,%str( )) then output &dsn.&i;
%end;
run;

[AshokD]

Thanks @Tom

It works now, You've saved me.