Migrating SAS code to Viya? Discover why the BY statement behaves differently in Cloud Analytic Services (CAS) due to distributed partitioning, and learn how to force stable row ordering
Examples use generated data (datalines) or SASHELP.
1 Code Block
DATA STEP Data
Explanation : This example demonstrates the concatenation of two tables (animal1 and plant1) using the SET statement without the BY statement. Rows from 'plant1' are simply appended to the end of 'animal1'. This is a basic operation for combining data.
Copied!
data animal1;
input Common $ Animal $ 3-8 Number 10-11;
datalines;
a Ant 1
b Bird 2
c Cat 3
d Dog 4
e Eagle 5
f Frog 6
g Goose 7
h Hawk 8
i Impala 9
;
data plant1;
input Common $ Plant $ 3-10 Number 12-13;
datalines;
a Grape 1
c Hazelnut 2
e Indigo 3
g Jicama 4
i Kale 5
;
/* Concaténer animal1 et plant1 */
data append_basique;
set animal1 plant1;
run;
proc print data=append_basique;
title '1. Concaténation de tables Animal et Plant (Base SAS)';
run;
1
DATA animal1;
2
INPUT Common $ Animal $ 3-8 Number 10-11;
3
DATALINES;
4
a Ant 1
5
b Bird 2
6
c Cat 3
7
d Dog 4
8
e Eagle 5
9
f Frog 6
10
g Goose 7
11
h Hawk 8
12
i Impala 9
13
;
14
15
DATA plant1;
16
INPUT Common $ Plant $ 3-10 Number 12-13;
17
DATALINES;
18
a Grape 1
19
c Hazelnut 2
20
e Indigo 3
21
g Jicama 4
22
i Kale 5
23
;
24
25
/* Concaténer animal1 et plant1 */
26
DATA append_basique;
27
SET animal1 plant1;
28
RUN;
29
30
PROC PRINTDATA=append_basique;
31
title '1. Concaténation de tables Animal et Plant (Base SAS)';
32
RUN;
2 Code Block
DATA STEP Data
Explanation : This example uses the BY statement with a single variable ('Common') to interleave records from the 'animal1' and 'plant1' tables. Rows are combined based on matching values of the 'Common' variable.
Copied!
data animal1;
input Common $ Animal $ 3-8 Number 10-11;
datalines;
a Ant 1
b Bird 2
c Cat 3
d Dog 4
e Eagle 5
f Frog 6
g Goose 7
h Hawk 8
i Impala 9
;
data plant1;
input Common $ Plant $ 3-10 Number 12-13;
datalines;
a Grape 1
c Hazelnut 2
e Indigo 3
g Jicama 4
i Kale 5
;
/* Entrelacer animal1 et plant1 par la variable COMMON */
data interleave_by_common;
set animal1 plant1;
by common;
run;
proc print data=interleave_by_common;
title '2. Entrelacement par la variable COMMON (Base SAS)';
run;
1
DATA animal1;
2
INPUT Common $ Animal $ 3-8 Number 10-11;
3
DATALINES;
4
a Ant 1
5
b Bird 2
6
c Cat 3
7
d Dog 4
8
e Eagle 5
9
f Frog 6
10
g Goose 7
11
h Hawk 8
12
i Impala 9
13
;
14
15
DATA plant1;
16
INPUT Common $ Plant $ 3-10 Number 12-13;
17
DATALINES;
18
a Grape 1
19
c Hazelnut 2
20
e Indigo 3
21
g Jicama 4
22
i Kale 5
23
;
24
25
/* Entrelacer animal1 et plant1 par la variable COMMON */
26
DATA interleave_by_common;
27
SET animal1 plant1;
28
BY common;
29
RUN;
30
31
PROC PRINTDATA=interleave_by_common;
32
title '2. Entrelacement par la variable COMMON (Base SAS)';
33
RUN;
3 Code Block
DATA STEP Data
Explanation : This example illustrates more advanced interleaving using the BY statement with two variables ('Common' and 'Number'). This allows for more granular control over the order of combining rows between the 'animal1' and 'plant1' tables.
Copied!
data animal1;
input Common $ Animal $ 3-8 Number 10-11;
datalines;
a Ant 1
b Bird 2
c Cat 3
d Dog 4
e Eagle 5
f Frog 6
g Goose 7
h Hawk 8
i Impala 9
;
data plant1;
input Common $ Plant $ 3-10 Number 12-13;
datalines;
a Grape 1
c Hazelnut 2
e Indigo 3
g Jicama 4
i Kale 5
;
/* Entrelacer animal1 et plant1 par COMMON et Number */
data interleave_by_common_number;
set animal1 plant1;
by common Number;
run;
proc print data=interleave_by_common_number;
title '3. Entrelacement par COMMON et Number (Base SAS)';
run;
1
DATA animal1;
2
INPUT Common $ Animal $ 3-8 Number 10-11;
3
DATALINES;
4
a Ant 1
5
b Bird 2
6
c Cat 3
7
d Dog 4
8
e Eagle 5
9
f Frog 6
10
g Goose 7
11
h Hawk 8
12
i Impala 9
13
;
14
15
DATA plant1;
16
INPUT Common $ Plant $ 3-10 Number 12-13;
17
DATALINES;
18
a Grape 1
19
c Hazelnut 2
20
e Indigo 3
21
g Jicama 4
22
i Kale 5
23
;
24
25
/* Entrelacer animal1 et plant1 par COMMON et Number */
26
DATA interleave_by_common_number;
27
SET animal1 plant1;
28
BY common Number;
29
RUN;
30
31
PROC PRINTDATA=interleave_by_common_number;
32
title '3. Entrelacement par COMMON et Number (Base SAS)';
33
RUN;
4 Code Block
DATA STEP / PROC CASUTIL Data
Explanation : This example demonstrates how to perform table interleaving in the CAS environment. It starts by creating base SAS tables, loads them as CAS tables using PROC CASUTIL, then uses a DATA step with the BY statement to interleave the tables directly in distributed memory. The processing order in CAS may differ from Base SAS due to parallelism and partitioning. The `_hostname_` variable is added to observe on which node the data was processed, illustrating the distributed nature of CAS.
Copied!
libname mycas cas;
data animal_cas;
input Common $ Animal $ 3-8 Number 10-11;
datalines;
a Ant 1
b Bird 2
c Cat 3
d Dog 4
e Eagle 5
f Frog 6
g Goose 7
h Hawk 8
i Impala 9
;
data plant_cas;
input Common $ Plant $ 3-10 Number 12-13;
datalines;
a Grape 1
c Hazelnut 2
e Indigo 3
g Jicama 4
i Kale 5
;
proc casutil;
load data=animal_cas outcaslib='CASUSER' casout='ANIMAL_CAS' replace;
load data=plant_cas outcaslib='CASUSER' casout='PLANT_CAS' replace;
run;
/* Entrelacer les tables CAS par la variable COMMON */
data mycas.interleaveByCommonCas;
set mycas.animal_cas mycas.plant_cas;
by common;
/* La variable _hostname_ peut être utile pour observer la distribution */
hostname=_hostname_;
run;
proc print data=mycas.interleaveByCommonCas;
title '4. Entrelacement par COMMON dans un DATA Step en CAS';
run;
/* Nettoyage des tables CAS */
proc casutil;
droptable casdata='ANIMAL_CAS' incaslib='CASUSER';
droptable casdata='PLANT_CAS' incaslib='CASUSER';
droptable casdata='INTERLEAVEBYCOMMONCAS' incaslib='CASUSER';
run;
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.
Related Documentation
Aucune documentation spécifique pour cette catégorie.
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.
