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.
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;
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;
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';
"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.
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";
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;
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;
"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?
In the dictionary tables (and the sashelp views based on them), library and dataset names are always in capitals.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.