topic Re: read all datasets in a library and conditionally split them in SAS Programming

read all datasets in a library and conditionally split them

vraj1 — Fri, 18 May 2018 13:25:40 GMT

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.

Re: read all datasets in a library and conditionally split them

RW9 — Fri, 18 May 2018 13:39:13 GMT

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;

Re: read all datasets in a library and conditionally split them

Kurt_Bremser — Fri, 18 May 2018 13:43:44 GMT

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;

Re: read all datasets in a library and conditionally split them

vraj1 — Fri, 18 May 2018 13:48:02 GMT

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';

Re: read all datasets in a library and conditionally split them

RW9 — Fri, 18 May 2018 13:50:14 GMT

"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.

Re: read all datasets in a library and conditionally split them

Kurt_Bremser — Fri, 18 May 2018 13:50:40 GMT

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

Re: read all datasets in a library and conditionally split them

vraj1 — Fri, 18 May 2018 13:53:14 GMT

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";

Re: read all datasets in a library and conditionally split them

vraj1 — Fri, 18 May 2018 14:00:12 GMT

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;

Re: read all datasets in a library and conditionally split them

RW9 — Fri, 18 May 2018 14:00:50 GMT

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;

Re: read all datasets in a library and conditionally split them

RW9 — Fri, 18 May 2018 14:02:49 GMT

"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?