Author: tom

Today, I played with the new SAS Viya environment. This environment has been promoted as a cloud based environment by SAS that acts as a SAAS solution to analytics needs from an organisation. This implies that we only need an internet connection and a browser to access this environment. How does it work. A small…

I found one possibility to gracefully end a SAS job. The code is rather straight forward. One opens a macro with a condition statement %if %then etc. If the condition is fulfilled, the code may run as previously anticipated. If not, a return statement ends processing the procedure. %let dsn=work.a; %macro abortIfRemaining; %if (%sysfunc(exist(&dsn))) %then…

Similarly to Oracle connect by level (see also http://van-maanen.com/index.php/2016/04/08/an-oracle-programme-with-a-loop/ ), Teradata has a structure whereby tables can be connect in order to create a potentially large table. Let us show some code: with RECURSIVE jaren(belastingjaar) as ( select belastingjaar from (select 2015 as belastingjaar)A union all select belastingjaar+1 from jaren where belastingjaar < 2020 )…

SAS currently allows to create simple reports via a web portal. This works as follows. Assume, one has an environment where the correct installation is made. One may then invoke the web report studio via call from the browser. In my case. it is http://sasspeel:80/SASWebReportStudio. The advantages are clear. One may share the report across…

Teradata has a nice command to get a quick overview on tables. This command is as follows: help statistics table_name. In the output, I see indicators like the data and time of the creation, the number of unique rows etc. This command can be combined with another command like: select * from dbc.tables where databasename…

One may use the SAS Management Console to deploy SAS jobs. The trick is as follows. One must have a SAS job that could be either created from the SAS programme or could be exported from Enterprise Guide. Let me provide an example of such a programme: This can be deployed, using in SAS Management…

Today, I learned something that could be quite handy. Quite often I wanted the like condition on two conditions. I then created a SQL statement like: select * from XXX where field like ‘%ni%’ or field like ‘%NI%’. This can done in a less complicated formula. This looks like select * from XXX where field…

Recently I noticed that an error in SAS does not automatically lead to wroung outcomes. Look what happens. I created two tables that are sorted by a field (nummer). proc import datafile = ‘C:\Users\tomva\SynologyDrive\SAS\input.xlsx’dbms=xlsx out=input replace;run; proc means data=input sum noprint;by nummer;var aantal;output out=sumtot sum=;run; data sumtot(drop=_type_ _freq_); set sumtot(rename=(aantal=sum_aantal)); run; proc sort data=sumtot;by nummer;run;proc…

This short article describes how to add two files with data to a SAS dataset. Assume both files use the same format for the data. The first approach uses a more or less standard technique wereby a global variable (fileref) is created that refers to two files. filename gegs (‘C:\Users\tomva\SynologyDrive\SAS\gegs.txt’ ‘C:\Users\tomva\SynologyDrive\SAS\gegs2.txt’); data ff; infile gegs;…

Recently, I came across the so-called fast export facility in Teradata. This facility allows you to export data from Teradata in a relative fast way. Let us look at the script: .LOGTABLE tom.invoer_log; .logon 192.168.178.13/tom,******; database tom; .BEGIN EXPORT SESSIONS 2; .EXPORT OUTFILE C:\Users\tomva\Documents\invoer.txt MODE RECORD FORMAT TEXT; SELECT CAST(leeftijd AS CHAR(10)), CAST(nummer AS CHAR(10))…