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;

SAS_Views

Q)What are different SAS VIEWS.
Q)What is the difference between SAS File and SAS View
Q)Describe PROC SQL and Data Step view

The main difference between SAS data files and SAS data views is where the data

values are stored.

A SAS data file contains both descriptor and the data values
A SAS data view  contains only the descriptor information about the data and instructions on how to retrieve data values that are stored elsewhere.

 

DATA Step View
A partially compiled DATA step program that can read data from different sources.

A DATA step view can be created only in a DATA step.

 

Creating a SAS DATA Step View
the DATA step is partially compiled and the intermediate code is stored in the specified SAS data library with a member type of VIEW.

data company.newdata / view=company.newdata;

infile <fileref>;

<DATA step statements>

run;

Referencing a SAS DATA Step View

the compiler resolves the intermediate code and generates executable code for thehost environment

the generated code is executed as the DATA or PROC step requests observations.

 

 

PROC SQL VIEW

CREATE VIEW proc-sql-view AS

SELECT column-1<, ... column-n>

FROM table-1 | view-1<, ... table-n | view-n>

<optional query clauses>;

 

You can use a DESCRIBE VIEW statement to display a definition of a view in the

SAS log.

proc sql;

describe view sasuser.faview;

 

data A B / view = A; set sasuser.Cars; if origin = 'European' then output A; else if origin = 'Cuba' then output B; run; data _null_; set A; run; proc print data = A; run;	there will be no error even if we don’t use yellow text …
data A B / view = A; set sasuser.Cars; if origin = 'European' then output A; else if origin = 'Cuba' then output B; run; data _null_; set A; run; proc print data = B; run;	there will be error if we don’t use yellow text … We need to refer VIEW sasuser.ranch once before referring to sasuser.condo.