read all datasets in a library and conditionally split them

[vraj1]

I have datasets in library and need to conditionally split all of them into 3 parts.

Ex- aghs consists union = "first" "second" "third" 

libraries inc, ind, inf 

if for aghs and union="first" then it should go to inc

if for aghs and union="second" then it should go to ind

if for aghs and union="third" then it should go to inf

i have more than one dataset. How can i optimally do this.

Any help please. 

[RW9]

Don't, its never a good idea to split same data into smaller bits, it takes more disk space, more resources, makes your coding life harder.

That said:

data _null_;
  set sashelp.vtable (where=(libname="YOURLIB"));
  call execute('data '||strip(memname)||'1; set yourlib.'||strip(memname)||'; where union="first"; run;');
  call execute('data '||strip(memname)||'2; set yourlib.'||strip(memname)||'; where union="second"; run;');
  call execute('data '||strip(memname)||'3; set yourlib.'||strip(memname)||'; where union="third"; run;');
run;

[vraj1]

Thanks, it is an integrated database and i want to seperate them and place them in seperate folders.

the code which you have provided gives

NOTE: The map data sets in library MAPSGFK are based on the digital maps from GfK GeoMarketing and are covered by their Copyright. For additional information, see
http://support.sas.com/mapsonline/gfklicense.
NOTE: There were 0 observations read from the data set SASHELP.VTABLE.
WHERE libname='work';

[RW9]

"NOTE: There were 0 observations read from the data set SASHELP.VTABLE.
WHERE libname='work';"

Check your where clause, libnames need to be in uppercase.

[vraj1]

Thanks a lot. Instead of dataset name as 1 ,2 and 3 can i place them in seperate folders as i made libname inc "path"; libname ind "path";

libname ing "path";

[RW9 · Accepted solution]

Of course, just change the generated code, call execute() is just a string, which is run as code after the datastep executes:

data _null_;
  set sashelp.vtable (where=(libname="YOURLIB"));
  call execute('data inc.'||strip(memname)||'; set yourlib.'||strip(memname)||'; where union="first"; run;');
  call execute('data ind.'||strip(memname)||'; set yourlib.'||strip(memname)||'; where union="second"; run;');
  call execute('data ing.'||strip(memname)||'; set yourlib.'||strip(memname)||'; where union="third"; run;');
run;

[vraj1]

i use the below libnames but it doesnt work.

data _null_;
set sashelp.vtable (where=(libname="WORK"));
call execute('data inc.'||strip(memname)'; set WORK.'||strip(memname)||'; where studyid="12709A"; run;');
call execute('data  ind.'||strip(memname)'; set WORK.'||strip(memname)||'; where studyid="12710A"; run;');
call execute('data ing.'||strip(memname)'; set WORK.'||strip(memname)||'; where studyid="12712A"; run;');
run;

[RW9]

"Doesn't work" - does not tell us anything.  What does the log say?  You should have three datasteps for each dataset in work.  Is the where clauses correct?

[Kurt_Bremser]

In the dictionary tables (and the sashelp views based on them), library and dataset names are always in capitals.

[Kurt_Bremser]

Write a datastep that splits one:

data
  inc.dataset
  ind.dataset
  inf.dataset
;
set aghs.dataset;
select (union);
  when ('first') output inc.dataset;
  when('second) output ind.dataset;
  when ('third') output inf.dataset;
  otherwise; /* for safety, remove if you want a tripwire */
end;
run;

identifiy variable parts, and use macro variable(s):

%let dataset=dataset;

data
  inc.&dataset
  ind.&dataset
  inf.&dataset
;
set aghs.&dataset;
select (union);
  when ('first') output inc.&dataset;
  when('second) output ind.&dataset;
  when ('third') output inf.&dataset;
  otherwise; /* for safety, remove if you want a tripwire */
end;
run;

convert to a macro:

%macro split_data(dataset);

data
  inc.&dataset
  ind.&dataset
  inf.&dataset
;
set aghs.&dataset;
select (union);
  when ('first') output inc.&dataset;
  when('second) output ind.&dataset;
  when ('third') output inf.&dataset;
  otherwise; /* for safety, remove if you want a tripwire */
end;
run;

%mend;

Now you need to create a dataset of datasets for your library:

proc sql;
create table control as
select memname from dictionary.tables
where libname = 'AGHS';
quit;

and call your macro off that:

data _null_;
set control;
call execute('%split_data(' !! strip(memname) !! ');');
run;