topic Re: create 100+ datasets from one dataset by splitting a variable in SAS Programming

create 100+ datasets from one dataset by splitting a variable

deleted_user — Fri, 01 May 2009 09:41:18 GMT

I have a dataset with 3 variables only and one variable is area name which has 100+ different areas. Is there an easy way of splitting the dataset into 100+ datasets instead of stacks of code.
I know how to do it using a macro but need to know how to create macro-variables from the area name variable attributes and loop into the macro rather than typing each one out.

Your help will be much appreciated.
Cheers

Re: create 100+ datasets from one dataset by splitting a variable

deleted_user — Fri, 01 May 2009 10:33:38 GMT

You just need something along the lines of.........

proc sql;
select count(area) into :maxarea
from tablename; /* I'm assuming distinct areas */

select area into :area1-:area%left&maxarea
from tablename;
quit;

and voila, a list of macro variables (area1 thru area(n)) for you to loop through

Re: create 100+ datasets from one dataset by splitting a variable

GertNissen — Fri, 01 May 2009 10:57:21 GMT

Perhaps you can find inspiration in this answer

http://support.sas.com/forums/thread.jspa?messageID=19230䬞
or
http://support.sas.com/forums/thread.jspa?messageID=19559䱧

Re: create 100+ datasets from one dataset by splitting a variable

data_null__ — Fri, 01 May 2009 11:08:17 GMT

What are you trying to do? I expect you can do without macro are at least without 100s of macro variables.

Re: create 100+ datasets from one dataset by splitting a variable

Peter_C — Tue, 05 May 2009 15:48:06 GMT

try [PRE] CLASS[/pre]and/or[pre] BY[/pre] statement processing in your analytic procedures.

If that is not enough, please explain how you want to use these 100+ data sets.

PeterC

Re: create 100+ datasets from one dataset by splitting a variable

deleted_user — Wed, 06 May 2009 10:25:59 GMT

Hi

I'm creating these datasets to use in the SAS forecasting application at area level.

The dataset has 130 areas and I need to split into individual area level datasets. Unfortunately I've not used PROC SQL before so would appreciate any help in Base SAS language.

Cheers

Re: create 100+ datasets from one dataset by splitting a variable

Peter_C — Wed, 06 May 2009 16:20:44 GMT

surely the forecasting application supports by-processing and classing?

You may find other forums more helpful.

Although it is a xxxxxxxx to manage them, it is easy to create hundreds of data sets[pre] data rest %gen( pattern= data.area###, from=1, upto= 1000 ) ;
set original.data_set ;
select (area) ;
%gen( pattern= %str( when ( ### ) output data.area### ; )
, from=1, upto= 1000 )
otherwise output rest ;
end;
run ;[/pre]
That will split the table original.data_set into up to 1001 datasets assuming that rows with "area" having value, say 23, should be written into table data.area23 .
Any rows with area outside the range 1-1000 will be written to table work.rest.
The code assumes you have a macro %GEN() available that works by replacing ### in a pattern with a number varying from &from to &upto. Something like:[pre]%macro gen( from=1, upto= 10, pattern= asdf###asdfg )/ des='pattern generator';
%local word pointer ;
%do pointer = &from %to &upTo ;
%sysfunc( tranwrd( %superq(pattern), ###, &pointer ))
%end ;
%mend gen ;[/pre]
but beware code offered over the internet you might want to test it on less than 100 datasets

PeterC

Re: create 100+ datasets from one dataset by splitting a variable

SASPhile — Wed, 06 May 2009 19:33:52 GMT

Try this:
%macro test;
data _null_;
set yourdatasetname end = eof;---->give Your dataset name
if eof then call symput('obs',_n_);--->gives total number of observations
xvar = compress('dsn' || _n_);
call symput (xvar,fieldname);---give the field(area) you should provide
run;

%do i= 1 %to &obs;
%put &&dsn&i; ---->This part is to test if the macro &&dsn&i resolves to correct values!!
%end;
%mend test;

%macro test1;

%do i= 1 %to &obs;
data &&dsn&i;

-----Your sas logic------

%end;

%mend test1;

Re: create 100+ datasets from one dataset by splitting a variable

deleted_user — Thu, 07 May 2009 09:44:31 GMT

Thankyou very much for all your help guys - I've managed to get it to work with a combination of methods suggested.

many thanks 😉