DATA Step, Macro, Functions and more

indexing

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 76
Accepted Solution

indexing

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

Accepted Solutions
Solution
‎04-30-2018 07:51 AM
Super User
Posts: 13,876

Re: indexing

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


All Replies
Super User
Super User
Posts: 9,799

Re: indexing

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.

Frequent Contributor
Posts: 76

Re: indexing

[ Edited ]

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

 

Valued Guide
Posts: 624

Re: indexing

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

Super User
Posts: 10,530

Re: indexing

And the definition of macro %test is still missing.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 76

Re: indexing

Posted in reply to KurtBremser
replace %test(&inLibref..&inMember.); with this ( if name ne ' ' then name='XXXXX'Smiley Wink
Super User
Super User
Posts: 9,799

Re: indexing

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

Frequent Contributor
Posts: 76

Re: indexing

%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);

 

Attachment
Attachment
Super User
Super User
Posts: 9,799

Re: indexing

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.

 

 

 

 

 

 

 








Super User
Posts: 10,530

Re: indexing

If you want to avoid sorting, you can use proc sql for the joins, but that will take time on its own.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 76

Re: indexing

Posted in reply to KurtBremser
yes, but my logic (%test macro) is in base sas
Solution
‎04-30-2018 07:51 AM
Super User
Posts: 13,876

Re: indexing

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?

☑ This topic is solved.

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

Discussion stats
  • 11 replies
  • 174 views
  • 0 likes
  • 5 in conversation