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

Hi ,

I want to insert some values into all tables in test library by using

%test macro. But some tables have indexes some tables doesn't  , when I am sorting the tables to merge , it is not creating the indexes (sorting will remove the index). I tried to get indexs from  (sashelp.vindex). transpose the values and concatenate them in one variable  and create a macro . I applied the indexes to the final dataset but the performance is too poor to apply for all the tables.

note:100 tables in test library with huge records.

 

%macro a (inLibref);
ods output Members=Members;
 proc datasets library=&inLibref memtype=data;
 run;
 quit;
    %local datasetCount iter inLibref inMember;

    /*get number of datasets*/
    proc sql noprint;
        select count(*)
         into :datasetCount
        from WORK.Members;
		%put " dataset count is:&datasetCount."
    quit;

    /*initiate loop*/
    %let iter=1;
    %do %while (&iter.<= &datasetCount.);
        data _NULL_;
            set WORK.Members (firstobs=&iter. obs=&iter.);
            call symput("inMember",strip(Name));
          
        run;
proc sort data=&inLibref..&inMember. ;
			by id;
		run;
		proc sort data=a;
			by id;
		run;
		data &inLibref..&inMember.;
			merge  &inLibref..&inMember.(in=a) a(in=b);
					by id;
					if a=b then do;
		%test(&inLibref..&inMember.);
					end;
			if a then output;
		run;
		%let iter=%eval(&iter.+1);
    %end;
proc datasets library=work;
   delete Members;
run;
quit;
%mend a;
%a(test);
1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

Are you perhaps looking for something like the INDEX CREATE statement in Proc Datasets to create an index on variables already in the data sets?

View solution in original post

11 REPLIES 11
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Sorry, your post is a bit confusing.  There is a call to %test in the code, but I don't see a test macro anywhere?

Second, I don't see anything "index" related here?

Third, it looks like your code is merging a dataset test, to every dataset in a library, is this right?  Not being able to see either test or the original datasets, I can't tell, but this does seem to be the most intensive method of adding items from one dataset to another.  Show some test data for base and for test (in the form of a datastep).   

 

Just as a tip, you can cut half the code off by simply:

data _null_;
  set sashelp.vtables (where=(libname="WORK"));
  call execute('data ...; set ...; run;');
run;

This will run the call execute for every member in work, with need to for counting, looping etc.

sathya66
Barite | Level 11

Thanks,

    base
id name  
1 qq  
2 aa  
3 ss  
4 ss  
5 dd  
6 dff  
7 vff  
8 gggg  
9 ggggg  
10 rrr  
11 sss  
12 ssss  
13 dddd  
14 sssss  

 

    a
id    
2    
5    
6    
7    
10    

 

%test macro

 

if name ne ' ' then name='XXXXX';


%macro a (inLibref);
	ods output Members=Members;

	proc datasets library=&inLibref memtype=data;
	run;

	quit;

	data Members(drop=memname rename=(name=memname));
		length libname $8.memname $32.;
		set Members;
		libname="&inLibref";
	run;

	data Vindex;
		set sashelp.Vindex;
		where libname="&inLibref";
	run;

	proc sort data=Vindex;
		by libname memname;
	run;

	proc sort data=Members;
		by libname memname;
	run;

	data Members(keep=indxname memname libname);
		merge Members(in=a) Vindex (in=b);
		by  libname memname;

		if a and b;
	run;

	proc transpose data=Members out=Members_1;
		by libname memname;
		var indxname;
	run;

	data Members(keep=memname index_var);
		set Members_1;
		index_var=compbl(cat(of COL:));
	run;

	%local datasetCount iter inLibref inMember;

	/*get number of datasets*/
	proc sql noprint;
		select count(*)
			into :datasetCount
				from WORK.Members;
		%put " dataset count is:&datasetCount."
		quit;

		/*initiate loop*/
		%let iter=1;

		%do %while (&iter.<= &datasetCount.);

	data _NULL_;
		set WORK.Members (firstobs=&iter. obs=&iter.);
		call symput("inMember",strip(Name));
		call symput("index",index_var);
	run;

	proc sort data=&inLibref..&inMember.;
		by id;
	run;

	proc sort data=a;
		by id;
	run;

	data &inLibref..&inMember.(index=(&index));
		merge  &inLibref..&inMember.(in=a) a(in=b);
		by id;

		if a=b then
			do;
				%test(&inLibref..&inMember.);
			end;

		if a then
			output;
	run;

	%let iter=%eval(&iter.+1);
		%end;

		proc datasets library=work;
			delete Members;
		run;

		quit;

%mend a;

%a(test);

 

andreas_lds
Jade | Level 19

Sorry, but this code-block is totally unreadable. Please post formatted code using {i} icon above the text box.

sathya66
Barite | Level 11
replace %test(&inLibref..&inMember.); with this ( if name ne ' ' then name='XXXXX';)
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Sorry, you will need to sort that code block out.

Pleas post test data in the form of a datastep, with some relevant data.  You show and empty base dataset, and another empty one.

With that all I can suggest is:

data _null_;
  set sashelp.vtable (where=(libname="WORK"));
  call execute(cat('data ',memname,'; merge ',memname,' test; by id; if a and b; run;'));
run; 

This will create for each dataset in work, the datastep code to merge that dataset with test based on ID

sathya66
Barite | Level 11
%macro a (inLibref);
ods output Members=Members;
 proc datasets library=&inLibref memtype=data;
 run;
 quit;
 data Members(drop=memname rename=(name=memname));
 length libname $8.memname $32.  ;
 set Members;
libname="&inLibref";
run;
data Vindex;
set sashelp.Vindex;
where libname="&inLibref";
run;
proc sort data=Vindex;
by libname memname ;
run;
proc sort data=Members ;
by libname memname ;
run;
data Members(keep=indxname memname libname)  ;
merge Members(in=a) Vindex (in=b);
by  libname memname ;
if a and b  ;
run;

proc transpose data=Members out=Members_1 ;
by libname memname;
var indxname;
run;


data Members(keep=memname index_var);
set Members_1;
index_var=compbl(cat(of COL:)); 
run;
    %local datasetCount iter inLibref inMember;

    /*get number of datasets*/
    proc sql noprint;
        select count(*)
         into :datasetCount
        from WORK.Members;
		%put " dataset count is:&datasetCount."
    quit;

    /*initiate loop*/
    %let iter=1;
    %do %while (&iter.<= &datasetCount.);
        data _NULL_;
            set WORK.Members (firstobs=&iter. obs=&iter.);
            call symput("inMember",strip(Name));
			call symput("index",index_var);
          
        run;
proc sort data=&inLibref..&inMember. ;
			by id;
		run;
		proc sort data=a;
			by id;
		run;
		data &inLibref..&inMember.(index=(&index));
			merge  &inLibref..&inMember.(in=a) a(in=b);
					by id;
					if a=b then do;
		if name ne ' ' then name='XXXXX';
					end;
			if a then output;
		run;
		%let iter=%eval(&iter.+1);
    %end;
proc datasets library=work;
   delete Members;
run;
quit;
%mend a;
%a(test);

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

I will try again,

Please post test data in the form of a datastep, with some relevant data.  You show and empty base dataset, and another empty one.

With that all I can suggest is:

data _null_;
  set sashelp.vtable (where=(libname="WORK"));
  call execute(cat('data ',memname,'; merge ',memname,' test; by id; if a and b; run;'));
run; 

This will create for each dataset in work, the datastep code to merge that dataset with test based on ID

 

You do not need any of the code you present, the datastep shown will do exactly the same thing, you just need to alter it slightly to your specific task.

 

 

 

 

 

 

 








sathya66
Barite | Level 11
yes, but my logic (%test macro) is in base sas
ballardw
Super User

Are you perhaps looking for something like the INDEX CREATE statement in Proc Datasets to create an index on variables already in the data sets?

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
  • 11 replies
  • 1222 views
  • 0 likes
  • 5 in conversation