DATA Step, Macro, Functions and more

read all datasets in a library and conditionally split them

Accepted Solution Solved
Reply
Regular Contributor
Posts: 214
Accepted Solution

read all datasets in a library and conditionally split them

[ Edited ]

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. 


Accepted Solutions
Solution
‎05-18-2018 10:03 AM
Super User
Super User
Posts: 9,853

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

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;

View solution in original post


All Replies
Super User
Super User
Posts: 9,853

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

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;
Regular Contributor
Posts: 214

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

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

Super User
Super User
Posts: 9,853

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

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

Regular Contributor
Posts: 214

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

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

 

Solution
‎05-18-2018 10:03 AM
Super User
Super User
Posts: 9,853

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

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;
Regular Contributor
Posts: 214

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

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;

Super User
Super User
Posts: 9,853

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

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

Super User
Posts: 10,588

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,588

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

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 194 views
  • 0 likes
  • 3 in conversation