DATA Step, Macro, Functions and more

create 100+ datasets from one dataset by splitting a variable

Reply
N/A
Posts: 0

create 100+ datasets from one dataset by splitting a variable

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
N/A
Posts: 0

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

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
SAS Employee
Posts: 160

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

Respected Advisor
Posts: 3,777

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

What are you trying to do? I expect you can do without macro are at least without 100s of macro variables.
Valued Guide
Posts: 2,175

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

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
N/A
Posts: 0

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

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
Valued Guide
Posts: 2,175

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

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
Super Contributor
Posts: 647

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

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;
N/A
Posts: 0

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

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

many thanks ;-)
Ask a Question
Discussion stats
  • 8 replies
  • 211 views
  • 0 likes
  • 5 in conversation