03-29-2024
briankinnebrew
SAS Employee
Member since
06-05-2015
- 28 Posts
- 1 Likes Given
- 0 Solutions
- 6 Likes Received
-
Latest posts by briankinnebrew
Subject Views Posted 4079 12-02-2021 03:59 PM 5583 11-19-2021 02:15 PM 10856 08-09-2021 11:02 AM 14873 06-02-2021 03:35 PM 15875 02-23-2021 10:27 AM 15888 02-23-2021 09:50 AM 3782 02-05-2021 08:10 AM 16706 12-24-2020 09:36 AM 21652 11-24-2020 07:46 AM 14997 11-20-2020 11:37 AM -
Activity Feed for briankinnebrew
- Liked 2nd Place Winner - 2023 Customer Awards: Taurex Drill Bits - Rookie of the Year for DefaultDevers. 06-16-2023 09:59 AM
- Posted An alternative approach to check the scope of CAS tables on SAS Communities Library. 12-02-2021 03:59 PM
- Posted Tired of deleting temporary CAS tables? Let CAS do it for you. on SAS Communities Library. 11-19-2021 02:15 PM
- Posted Re: Best Practices for Upgrading to the Latest Version of SAS®9? Q on Ask the Expert. 08-09-2021 11:02 AM
- Posted Re: The SAS 9 Content Assessment Tool on SAS Communities Library. 06-02-2021 03:35 PM
- Posted Re: The SAS 9 Content Assessment Tool on SAS Communities Library. 02-23-2021 10:27 AM
- Posted Re: The SAS 9 Content Assessment Tool on SAS Communities Library. 02-23-2021 09:50 AM
- Posted Re: What Are Best Practices for Upgrading to the Latest Version of SAS®9? on Administration and Deployment. 02-05-2021 08:10 AM
- Got a Like for Re: What Are Best Practices for Upgrading to the Latest Version of SAS®9?. 02-05-2021 08:10 AM
- Posted Re: The SAS 9 Content Assessment Tool on SAS Communities Library. 12-24-2020 09:36 AM
- Posted Re: SAS 9.4m7 is here! on SAS Communities Library. 11-24-2020 07:46 AM
- Got a Like for What Are Best Practices for Upgrading to the Latest Version of SAS®9?. 11-23-2020 11:23 AM
- Posted Best Practices for Upgrading to the Latest Version of SAS®9? Q&A, Slides, and On-Demand Recording on Ask the Expert. 11-20-2020 11:37 AM
- Posted Re: SAS 9.4m7 is here! on SAS Communities Library. 11-10-2020 09:14 AM
- Got a Like for What Are Best Practices for Upgrading to the Latest Version of SAS®9?. 11-09-2020 10:45 PM
- Got a Like for What Are Best Practices for Upgrading to the Latest Version of SAS®9?. 11-09-2020 06:45 PM
- Got a Like for What Are Best Practices for Upgrading to the Latest Version of SAS®9?. 11-09-2020 12:43 PM
- Posted What Are Best Practices for Upgrading to the Latest Version of SAS®9? on Administration and Deployment. 11-09-2020 12:16 PM
- Posted Re: The SAS 9 Content Assessment Tool on SAS Communities Library. 08-26-2020 01:41 PM
- Posted Re: The SAS 9 Content Assessment Tool on SAS Communities Library. 08-26-2020 01:34 PM
-
Posts I Liked
Subject Likes Author Latest Post 56 -
My Liked Posts
Subject Likes Posted 1 02-05-2021 08:10 AM 4 11-09-2020 12:16 PM 1 06-17-2015 02:12 PM -
My Library Contributions
Subject Likes Author Latest Post 2 1 3 10 7
02-06-2023
10:45 AM
@pchegoor Thank you for this suggestion. We'll talk with Brian about the possibility of doing a webinar on moving to 9.4M8.
... View more
11-14-2022
09:32 AM
@Debi_SAS It's currently scheduled for release in Jan 2023, and we'll have more details then. Most of the changes are to update underlying components to newer/more modern versions.
... View more
11-03-2022
12:45 PM
thanks Brian, great post that I guided my class to, when they questioned the value of dropping tables
... View more
12-02-2021
03:59 PM
2 Likes
Chances are, your Viya environment contains many caslibs and many tables within these caslibs. Determining the scope of these tables is extremely important. This affects your downstream processing. You don’t want to delete, overwrite, or alter session scope tables by accident. Conversely, you don’t want to delete promoted tables, especially if they are being accessed by more than one user.
One handy way to determine the scope of a CAS table is via the tableInfo CAS action. It provides valuable information about a particular table including whether it has been promoted. The tableInfo action output is written to the results tab in SAS Studio. An alternative approach is to use the tableExists CAS action. This action does not produce any output in the results tab. It merely sends a numeric code to a result table. Subsequently the result table can be queried to discover the scope of the CAS table.
The following example demonstrates how to leverage this approach.
First, identify your caslib and CAS table in the two %let statements. Once the code is executed, a result of 0, 1, or 2 will be written to the result table “r”. If the table doesn’t exist, a code of 0 will be written to the result table. A code of 1 will be written for session scoped tables and a code of 2 will be written for globally (promoted) scoped tables. Using conditional logic coupled with the “exists” dictionary name, a message will be written to the SAS log based on the corresponding code value. For example, if the CARS dataset is written to the CASUSER caslib as a session scoped table, the following note will be displayed in the SAS log:
The CAS table CARS has a session scope.
NOTE: PROCEDURE CAS used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds
If the CLASS dataset is written to the CASUSER caslib as a globally scoped table, the following note will be displayed in the SAS log:
The CAS table CLASS has a global scope.
NOTE: PROCEDURE CAS used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds
Lastly, if you search for a table that is not in CAS, the following message will appear in the SAS log:
The CAS table XYZ does not exist.
NOTE: PROCEDURE CAS used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds
If it helps you, consider using this technique when checking the scope of your CAS tables. Happy coding!
... View more
06-02-2021
03:35 PM
This tool runs on Windows as well as Linux and AIX.
... View more
02-05-2021
08:10 AM
1 Like
@IT_RUN_SAS
Hello Eric,
You can access the webinar on demand here: https://www.sas.com/en_us/webinars/best-practices-sas9.html. Simply sign in or register with your SAS profile.
Cheers,
Brian
... View more
07-08-2020
02:17 PM
Hello @telligent,
The SAS Communities are for discussions, collaboration and Q&A for SAS products and programming. You can search the SAS Programming and SAS Data Management communities for information on HiveQL.
Thank you.
... View more
06-01-2020
09:21 AM
Hello Ramprakash, Thank you for your feedback on my article. To use the CNTL= option in CASL, you would place right after the execDirect action. proc cas; fedsql.execDirect cntl={nowarn=true} /; query= " select * from TABLE3 ; " ; quit; I hope this helps.
... View more
11-07-2019
04:36 PM
3 Likes
PROC CASUTIL has a variety of capabilities. However, most of us likely use it to simply load data into CAS and possibly replace or promote a table to global scope. The CASUTIL procedure can load data sets from SAS libraries and files from multiple data sources associated with a CASLIB. However, SAS Data sets and external files do not have to be associated with a CASLIB. PROC CASUTIL can use a libname engine (via the LOAD DATA statement) or an external file or fileref (via the LOAD FILE statement). No input CASLIB is required for either of these approaches. PROC CASUTIL can also save CAS tables to the data source associated with a CASLIB, provide information on CAS tables and data source files, and drop CAS tables and delete files from the data source associated with a CASLIB.
Beginning with SAS Viya 3.5, several new parameters and options are available in the CASUTIL procedure. Code examples are provided to illustrate some of these new capabilities.
The new INDEX statement provides the capability to create indexes on one or more variables. The INDEXVARS statement specifies the list of variable(s) to create indexes for in your output CAS table.
/* LOAD data to CAS then create INDEX */
proc casutil sessref=mysess;
load casdata="cars.sas7bdat" outcaslib="casuser" casout="cars" replace
importoptions=(filetype="basesas" dtm="auto" debug="dmsglvli");
quit;
proc casutil sessref=mysess;
index casdata="cars" incaslib="casuser" outcaslib="casuser" casout="cars_index" replace
indexvars={"make", "origin"};
quit;
OR
/* Create INDEX during LOAD to CAS */
proc casutil sessref=mysess;
load casdata="cars.sas7bdat" outcaslib="casuser" casout="cars" replace
importoptions=(filetype="basesas" dtm="auto" debug="dmsglvli");
index casdata="cars" incaslib="casuser" outcaslib="casuser" casout="cars_index" replace
indexvars={"make", "origin"};
quit;
In either case, the log will display the following message:
NOTE: The Cloud Analytic Services server processed the request in 0.064576 seconds.
NOTE: Indexes were successfully added to the table "cars_index" in the "casuser" caslib.
Output from a PROC CONTENTS will show the indexes that have been applied.
The new ALTERTABLE statement enables you to modify your CAS tables. You can perform operations such as:
RENAME your CAS table.
Specify a new LABEL for your CAS table.
DROP or KEEP variables.
Specify the order of the columns with the COLUMNORDER statement.
Modify specific COLUMNS such as assigning a new format, label, or name.
NOTE: Only one of the DROP, KEEP, or COLUMNORDER parameters can be used at one time.
/* LOAD data to CAS then ALTER table */
proc casutil sessref=mysess;
load casdata="cars.sas7bdat" outcaslib="casuser" casout="cars" replace
importoptions=(filetype="basesas" dtm="auto" debug="dmsglvli");
quit;
proc casutil sessref=mysess;
altertable casdata="cars" incaslib="casuser" label="Altered CARS Table"
rename="cars_altered"
/* keep={"make", "model", "type", "origin"} */
columnorder={"origin", "make", "model", "type"};
quit;
OR
/* ALTER table during LOAD to CAS */
proc casutil sessref=mysess;
load casdata="cars.sas7bdat" outcaslib="casuser" casout="cars" replace
importoptions=(filetype="basesas" dtm="auto" debug="dmsglvli");
altertable casdata="cars" incaslib="casuser" label="Altered CARS Table"
rename="cars_altered"
/* keep={"make", "model", "type", "origin"} */
columnorder={"origin", "make", "model", "type"};
quit;
In either case, the log will display the following message:
NOTE: The Cloud Analytic Services server processed the request in 0.027774 seconds.
NOTE: Table "cars" was successfully renamed in the "casuser" caslib to table "cars_altered".
NOTE: Label for table "cars" in the "casuser" caslib was successfully updated to "Altered CARS Table".
NOTE: Column information for table "cars" in the "casuser" caslib was successfully updated.
The existing LOAD statement has been upgraded to support reading external files using a filref. It also supports the WRITETRANSFERSIZE= option, specifying the maximum number of bytes written during the data transfer to CAS.
The new PARTITION statement enables you to partition a table during the data transfer to CAS.
Use the optional PARTITIONBY | GROUPBY statements to name the variables to use for grouping.
/* LOAD data to CAS then PARTITION */
proc casutil sessref=mysess;
load casdata="cars.sas7bdat" outcaslib="casuser" casout="cars" replace
importoptions=(filetype="basesas" dtm="auto" debug="dmsglvli");
quit;
proc casutil sessref=mysess;
partition casdata="cars" incaslib="casuser" outcaslib="casuser" casout="cars_part" replace
partitionby=(make origin);
quit;
OR
/* PARTITION data during LOAD to CAS */
proc casutil sessref=mysess;
load casdata="cars.sas7bdat" outcaslib="casuser" casout="cars" replace
importoptions=(filetype="basesas" dtm="auto" debug="dmsglvli");
partition casdata="cars" incaslib="casuser" outcaslib="casuser" casout="cars_part" replace
partitionby=(make origin);
quit;
In either case, the log will display the following message:
NOTE: The Cloud Analytic Services server processed the request in 0.07634 seconds.
NOTE: The table "cars_part" in the "casuser" caslib was successfully partitioned.
The existing PROMOTE statement has been enhanced to support the QUIET option if you want to suppress the status and severity messages.
The new UPDATE statement allows you to update rows in a CAS table.
Use the SET statement to specify the variable to update and the value to set. In conjunction with the WHERE parameter, updates can be limited to specific rows.
/* LOAD data to CAS then UPDATE */
proc casutil sessref=mysess;
load casdata="cars.sas7bdat" outcaslib="casuser" casout="cars" replace
importoptions=(filetype="basesas" dtm="auto" debug="dmsglvli");
quit;
proc casutil sessref=mysess;
update casdata="cars" incaslib="casuser"
set={{var="cylinders", value="12"}}
where="invoice > 30000";
quit;
OR
/* UPDATE data during LOAD to CAS */
proc casutil sessref=mysess;
load casdata="cars.sas7bdat" outcaslib="casuser" casout="cars" replace
importoptions=(filetype="basesas" dtm="auto" debug="dmsglvli");
update casdata="cars" incaslib="casuser"
set={{var="cylinders", value="12"}}
where="invoice > 30000";
quit;
Partial output of the updated table.
There are many more enhancements to existing procedures and action sets in SAS Viya 3.5. Please consult the documentation on www.sas.com once SAS Viya 3.5 is available in November 2019.
... View more
10-30-2019
03:25 PM
2 Likes
This article was co-authored by Brian Kinnebrew and Steven Sober.
Did you miss the Ask the Expert session Best Practices in Migrating SAS Code to Leverage CAS? Not to worry, you can catch it on-demand at your leisure. A pdf of the presentation slides is attached.
The SAS Platform is greatly enhanced by SAS Viya. In this session, Steven Sober and Brian Kinnebrew share their experiences in leveraging SAS Viya to improve SAS processes. They explore technical insights and implementation details to CAS-enable your code, thus achieving faster results. These techniques are illustrated by real-life case studies.
In this session, you’ll learn the answers to questions such as:
If a process cannot run in CAS, where does it run?
What coding components are most frequently encountered in code reviews with customers?
Figure 1 Agenda of the Webinar Best Practices for Migrating SAS Code to leverage CAS
Here are some highlighted questions from the Q&A segment held at the end of the session for ease of reference.
What does CAS stand for? CAS stands for SAS Cloud Analytic Services. It's the In-memory processing engine in SAS Viya.
In general, if the base procedure is threaded in Base SAS, will it thread in CAS? Like Proc Sort, for example?
Yes, CAS is multi-threaded by default.
How can you tell if SPRE or CAS is running?
In the SAS log, there is a note that tells you if the procedure or DATA Step ran in CAS. If you write CASL code, that code will only run in CAS.
Is there a PROPCASE function in CAS?
This function is not supported in a DATA Step that runs in CAS. https://go.documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=lefunctionsref&docsetTarget=n169kj2of5nur0n1bl1hubv55q63.htm&locale=en
Does a LIBNAME statement run in SPRE, and a CASLIB will run in CAS?
All data sources that are access by a LIBNAME will be processed in SPRE. All data sources that are accessed via a CASLIB will run in CAS provided.
a. The step is a CAS enabled procedure or all statements in the DATA Step are CAS enabled.
b. All source and target tables to the step reside in CAS.
Recommended Resources
Course: Free SAS Viya Enablement
Course: SAS Viya Learning Path
Reading material:
Parallel Programming with the DATA Step: Next Steps (2018) required reading
How to achieve repeatable results with distributed DATA Step BY Groups (2018) required reading
Six reasons you should stop using the RANUNI function to generate random numbers (2013) required reading
The following Base SAS procedures are CAS Enabled (2018)
SAS ® SAS Viya ™ 3: FedSQL Programming for SAS ® Cloud Analytic Services (2018)
FedSQL Implicit Pass-Through Facility in CAS (2018)
What’s New in SAS® Data Connectors for SAS® SAS Viya® (2018)
How to Emulate DESCENDING BY Variables in DATA Step Code that Runs Distributed in SAS® SAS Viya™ (2018)
DATA Step in SAS® SAS Viya™: Essential New Features (2017)
How to Emulate PROC APPEND in CAS (2017)
Getting your SAS 9 code to run multi-threaded in SAS Viya 3.3 (2017)
Trial: Get an early preview of SAS® Visual Analytics on SAS® Viya™
Want more tips? Be sure to subscribe to the Ask the Expert board to receive follow up Q/A, slides and recordings from other SAS
Ask the Expert webinars. To subscribe, select Subscribe from the Options drop down button above the articles.
... View more
Labels:
05-01-2018
04:16 AM
You could be slightly more succinct by simply using
if 0;
as in
data cars;
set sashelp.cars;
run;
proc ds2;
data cars_out / overwrite=yes;
method run();
set cars;
if origin = 'USA' then
if 0;
end;
enddata;
run;
quit;
Since 0 is always false
... View more
11-28-2016
11:11 AM
1 Like
DS2 BY-group processing groups the rows from input tables and orders the rows by values of one or more columns in the BY statement. With in-database processing, data is distributed on different data partitions. Each DS2 thread running inside the database has access to one data partition. Each DS2 thread can group and order only the rows in the same data partition. Consequently, the data partition might have only part of the entire group of data. You must do a final aggregation in the main data program. But, in some instances, it is necessary for each thread to process the entire group of data. The SAS In-Database Code Accelerator provides a way to redistribute the input table to the thread program with a BY statement so that the entire group of data resides on the same data partition. The PROC DS2 statement BYPARTITION argument controls whether the input data is re-partitioned. By default, the input data for the DS2 program is automatically re-partitioned by the first BY variable. All of the BY groups are in the same data partition and processed by the same thread. Each thread does the BY processing for the entire group of data. You might not need to do the final aggregation in the main data program. For more information, see “Interleaving” in SAS DS2 Language Reference<>, and the DS2 procedure in Base SAS Procedures Guide.
... View more