SAS_Indexes

Q)Explain the difference between PROC SQL INDEX and DATA STEP INDEX.

	PROC SQL INDEX	DATA STEP INDEX
Creating a Simple Index	create index EmpID on work.payrollmaster(empid);	data simple2 (index=(division empid)); set sasuser.empdata; run;
Creating a Unique Index	create unique index EmpID on work.payrollmaster(empid);	data simple2 (index=(division empid/unique)); set sasuser.empdata; run;
Creating a Composite Index	create unique index daily on work.marchflights(flightnumber,date);	data composite (index=(Empdiv=(division empid))); set sasuser.empdata; run;
Display Index specification	proc sql; describe table marchflights;   proc datasets nolist; contents data=sasuser.sale2000; quit;   proc contents data=sasuser.sale2000; run;	proc datasets nolist; contents data=sasuser.sale2000; quit;   proc contents data=sasuser.sale2000; run;
Determining Whether SAS Is Using an Index	options msglevel=i; proc sql; select * from marchflights where flightnumber=’182’;	options msglevel=i; proc print data=sasuser.revenue; where flightid ne ’IA11200’;
Force SAS to ignore the index	proc sql; select * from marchflights (idxwhere=no) where flightnumber=’182’;	options msglevel=i; proc print data=company.organization (idxwhere=no); where department=’Sales’; run;
Force PROC SQL to use the index daily instead of Date	proc sql; select * from marchflights (idxname=daily) where flightnumber=’182’;	options msglevel=i; proc print data=company.organization (idxname=daily); where department=’Sales’; run;

 

Q)How to manipulate indexes using Proc Datasets.

PROC DATASETS LIBRARY=libref <NOLIST>;

MODIFY SAS-data-set-name;

INDEX DELETE index-name;

INDEX CREATE index-specification;

QUIT;

 

PROC DATASETS <LIBRARY=libref> <NOLIST>;

CONTENTS DATA=<libref.>SAS-data-set-name;

QUIT;

PROC DATASETS LIBRARY=old-libref <NOLIST>;

COPY OUT=new-libref;

SELECT SAS-data-set-name;

QUIT;

PROC COPY OUT=new-libref IN=old-libref <MOVE>;

SELECT SAS-data-set-name(s);

RUN;

QUIT;

PROC DATASETS LIBRARY=libref <NOLIST>;

CHANGE old-data-set-name = new-data-set-name;

QUIT;

 

PROC DATASETS LIBRARY=libref <NOLIST>;

MODIFY SAS-data-set-name;

RENAME old-var-name-1 = new-var-name-1

<...old-var-name-n = new-var-name-n>;

QUIT;