The program evaluates the functionality of the `%mp_sortinplace` macro in two scenarios. The first scenario creates a temporary table `work.example` from `sashelp.classfit`, adds a primary key constraint and applies `%mp_sortinplace`, then verifies that the constraints are recreated and that the sort is effective (first observation 'Alfred'). The second scenario creates `work.example2` without a primary key constraint, applies `%mp_sortinplace`, and confirms that the operation proceeds without error and that the sort is also applied correctly.
Data Analysis
Type : MIXTE
The script uses `sashelp.classfit` as the initial data source to create temporary work tables (`work.example`, `work.example2`). Test results and extracted constraints are stored in other work tables (`work.testme`, `work.test_results`).
1 Code Block
PROC SQL / MACRO %mp_sortinplace Data
Explanation : This block initializes the first test scenario. It creates a new table `work.example` by copying the content of `sashelp.classfit`. A primary key constraint is then added to the `name` column. Finally, the `%mp_sortinplace` macro is called to sort this table in place.
Copied!
proc sql;
create table work.example as
select * from sashelp.classfit;
alter table work.example
add constraint pk primary key(name);
%mp_sortinplace(work.example)
1
PROC SQL;
2
create TABLE work.example as
3
select * from sashelp.classfit;
4
alter TABLE work.example
5
add constraint pk primary key(name);
6
%mp_sortinplace(work.example)
2 Code Block
MACRO %mp_getconstraints / %mp_assertdsobs Data
Explanation : This block checks constraint management. The `%mp_getconstraints` macro is used to extract constraint information from `work.example` and save it to `work.testme`. Then, the `%mp_assertdsobs` macro asserts that there is exactly one observation in `work.testme`, confirming that the primary key constraint was correctly detected and handled after sorting.
Explanation : This block validates the sorting result. It uses a `DATA _NULL_` to read the first value of the `name` column from the sorted table `work.example` and stores it in the macro variable `test1`. The `%mp_assert` macro is then used to verify that `test1` is indeed 'Alfred', which confirms that the table has been sorted in ascending alphabetical order by the `name` column.
Copied!
%let test1=0;
data _null_;
set work.example;
call symputx('test1',name);
stop;
run;
%mp_assert(
iftrue=(
%str(&test1)=%str(Alfred)
),
desc=Check if sort was appplied,
outds=work.test_results
)
1
%let test1=0;
2
DATA _null_;
3
SET work.example;
4
call symputx('test1',name);
5
stop;
6
RUN;
7
%mp_assert(
8
iftrue=(
9
%str(&test1)=%str(Alfred)
10
),
11
desc=Check IF sort was appplied,
12
outds=work.test_results
13
)
4 Code Block
PROC SQL / MACRO %mp_sortinplace Data
Explanation : This block initiates the second test scenario. It creates a new table `work.example2` from `sashelp.classfit`, this time without adding a primary key constraint. The `%mp_sortinplace` macro is called on this table to test its behavior in the absence of explicit constraints.
Copied!
proc sql;
create table work.example2 as
select * from sashelp.classfit;
%mp_sortinplace(work.example2)
1
PROC SQL;
2
create TABLE work.example2 as
3
select * from sashelp.classfit;
4
%mp_sortinplace(work.example2)
5 Code Block
MACRO %mp_assert / DATA STEP / MACRO %mp_assert
Explanation : This block contains two assertions for the second test. The first uses `%mp_assert` to verify that the `%mp_sortinplace` macro executed successfully (system return code `&syscc` equals 0) even without a primary key. The second assertion, via a `DATA _NULL_` and `%mp_assert`, re-verifies that the sort was applied correctly by confirming that the first value of `name` in `work.example2` is 'Alfred'.
Copied!
%mp_assert(
iftrue=(
%str(&syscc)=%str(0)
),
desc=Ensure no errors when no key exists,
outds=work.test_results
)
%let test2=0;
data _null_;
set work.example2;
call symputx('test2',name);
stop;
run;
%mp_assert(
iftrue=(
%str(&test2)=%str(Alfred)
),
desc=Check if sort was appplied when no index exists,
outds=work.test_results
)
1
%mp_assert(
2
iftrue=(
3
%str(&syscc)=%str(0)
4
),
5
desc=Ensure no errors when no key exists,
6
outds=work.test_results
7
)
8
9
%let test2=0;
10
DATA _null_;
11
SET work.example2;
12
call symputx('test2',name);
13
stop;
14
RUN;
15
%mp_assert(
16
iftrue=(
17
%str(&test2)=%str(Alfred)
18
),
19
desc=Check IF sort was appplied when no index exists,
20
outds=work.test_results
21
)
This material is provided "as is" by We Are Cas. There are no warranties, expressed or implied, as to merchantability or fitness for a particular purpose regarding the materials or code contained herein. We Are Cas is not responsible for errors in this material as it now exists or will exist, nor does We Are Cas provide technical support for it.
SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration. WeAreCAS is an independent community site and is not affiliated with SAS Institute Inc.
This site uses technical and analytical cookies to improve your experience.
Read more.