BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vraj1
Quartz | Level 8

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

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
Diamond | Level 26 RW9
Diamond | Level 26

"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
Quartz | Level 8

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

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
Diamond | Level 26 RW9
Diamond | Level 26

"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
Super User

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;

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
  • 9 replies
  • 2020 views
  • 0 likes
  • 3 in conversation