topic Re: indexing in SAS Programming

indexing

sathya66 — Wed, 18 Apr 2018 14:05:03 GMT

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

Re: indexing

RW9 — Wed, 18 Apr 2018 14:16:51 GMT

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.

Re: indexing

sathya66 — Wed, 18 Apr 2018 14:51:09 GMT

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

Re: indexing

andreas_lds — Wed, 18 Apr 2018 14:39:06 GMT

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

Re: indexing

Kurt_Bremser — Wed, 18 Apr 2018 14:40:24 GMT

And the definition of macro %test is still missing.

Re: indexing

RW9 — Wed, 18 Apr 2018 14:40:30 GMT

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

Re: indexing

Kurt_Bremser — Wed, 18 Apr 2018 14:43:06 GMT

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

Re: indexing

sathya66 — Wed, 18 Apr 2018 14:44:51 GMT

replace %test(&inLibref..&inMember.); with this ( if name ne ' ' then name='XXXXX';)

Re: indexing

sathya66 — Wed, 18 Apr 2018 14:54:17 GMT

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

Re: indexing

sathya66 — Wed, 18 Apr 2018 14:56:43 GMT

yes, but my logic (%test macro) is in base sas

Re: indexing

RW9 — Wed, 18 Apr 2018 16:17:04 GMT

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.

Re: indexing

ballardw — Thu, 19 Apr 2018 16:46:25 GMT

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?