BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
8 REPLIES 8
deleted_user
Not applicable
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
data_null__
Jade | Level 19
What are you trying to do? I expect you can do without macro are at least without 100s of macro variables.
Peter_C
Rhodochrosite | Level 12
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
deleted_user
Not applicable
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
Peter_C
Rhodochrosite | Level 12
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
SASPhile
Quartz | Level 8
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;
deleted_user
Not applicable
Thankyou very much for all your help guys - I've managed to get it to work with a combination of methods suggested.

many thanks 😉

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 829 views
  • 0 likes
  • 5 in conversation