Getting first megabytes of large file

May 10, 2018 Root Leave a comment

You can use the DOS-command TYPE and MORE to get the first megabytes or gigabytes of a large file e.g. of TXT, XML, CSV etc.
This is done by piping the result into another file, there by getting only the first part of the file piped into the new file.

The DOS-command below will start piping the large file into the smaller new file.

type LARGE_SOURCE_FILE | more > SMALL_DESTINATION_FILE

1	type LARGE_SOURCE_FILE \| more > SMALL_DESTINATION_FILE

You are not able to see the result from the DOS-command. You need to press the SPACE-key on your keyboard, because every time you press the SPACE-key a new page of the file will be showed and thereby piped into the SMALL_DESTINATION_FILE.
When you think you have done this enough times, then you have to press CTRL-C on your keyboard, this will terminate the type-command and you will have the SMALL_DESTINATION_FILE containing data from the LARGE_SOURCE_FILE.

Be aware that you will get the — more — pipe text in the file!

SAS, SQL

SQL-statement to find lowest observation in dataset

May 10, 2018 Root Leave a comment

The SAS SQL-statement below will create a dataset containing all information for the observation with the lowest age in the dataset SASHELP.CLASS.

proc sql;
     create table CLASS as
           select     *
           from SASHELP.CLASS
           group by AGE
           having AGE=min(AGE)
     ;
quit;

proc sql;

create table CLASS as

select *

from SASHELP.CLASS

group by AGE

having AGE=min(AGE)

;

quit;

The syntax for the SAS SQL-statement is showed below.

proc sql;
     create table <DESTINATION TABLE> as
           select     *
           from <SOURCE TABLE>
           group by <COLUMN TO SEARCH>
           having <COLUMN TO SEARCH>=<FUNCTION>(<COLUMN TO SEARCH>)
     ;
quit;

proc sql;

create table <DESTINATION TABLE> as

select *

from <SOURCE TABLE>

group by <COLUMN TO SEARCH>

having <COLUMN TO SEARCH>=<FUNCTION>(<COLUMN TO SEARCH>)

;

quit;

As you see the <FUNCTION> doesn’t have to be min (minimum), it can be any function working on the type of <COLUMN TO SEARCH> – numeric or char.

Security

Enterprise Password Managers

May 10, 2018 Root Leave a comment

Below is a list of enterprise password managers

https://www.vaultproject.io/
HashiCorp Vault secures, stores, and tightly controls access to tokens, passwords, certificates, API keys, and other secrets in modern computing. Vault handles leasing, key revocation, key rolling, and auditing. Through a unified API, users can access an encrypted Key/Value store and network encryption-as-a-service, or generate AWS IAM/STS credentials, SQL/NoSQL databases, X.509 certificates, SSH credentials, and more.

https://teampass.net/
eamPass is a Passwords Manager dedicated for managing passwords in a collaborative way by sharing them among team members.
Teampass offers a large set of features permitting to manage your passwords and related data in an organized way in respect to the access rights defined for each users.
Teampass is an Open-Source free to use product distributed in respect with OpenSource GNU GPL-3.0.

SAS

Info about SAS-datasets in the WORK-library

April 25, 2018 Root Leave a comment

In SAS Enterprise Guide it is not very easy to see the size and number of observations in datasets in the WORK-library.

The macro below looks in the DICTIONARY.TABLES and gets these info for the WORK-library. Be aware that it will not work for views, because it’s not doing and actual count of the SAS-datasets.

/********************************************************************************
Author        : 
Creation date : 
Description   : Gets info about datasets in the WORK-library.
Example       : %countwork(print);
*********************************************************************************
Input
-----
&print        : If not empty it will do a PROC PRINT of the dataset WORKDS created
                by the macro.
*********************************************************************************
Output
------
WORKDS        : Contains information about the datasets in the WORK-library.
********************************************************************************/
%macro CountWork(print);
          proc sql;
                   create table workds as
                             select    libname
                                                , memname
                                                , typemem
                                                , nobs format=commax10.0
                                                , filesize format=sizekmg.
                                                , nvar
                             from dictionary.tables
                             where libname eq 'WORK'
                             order by nobs
                   ;
          quit;
 
          %if &print. ne %then
          %do;
                   proc print data=workds;
                   run;
          %end;
%mend;

/********************************************************************************

Author :

Creation date :

Description : Gets info about datasets in the WORK-library.

Example : %countwork(print);

*********************************************************************************

Input

-----

&print : If not empty it will do a PROC PRINT of the dataset WORKDS created

by the macro.

*********************************************************************************

Output

------

WORKDS : Contains information about the datasets in the WORK-library.

********************************************************************************/

%macro CountWork(print);

proc sql;

create table workds as

select libname

, memname

, typemem

, nobs format=commax10.0

, filesize format=sizekmg.

, nvar

from dictionary.tables

where libname eq 'WORK'

order by nobs

;

quit;

%if &print. ne %then

%do;

proc print data=workds;

run;

%end;

%mend;

SAS

Microsoft Team Foundation Server (TFS) and SAS

April 9, 2018 Root Leave a comment

Below is shown how you can extract the current Microsoft Team Foundation Server (TFS) revision number for a given file into SAS and use it in your SAS program.

Be aware that Visual Studio 13 is used in the example below. It is uncertain if newer versions of Visual Studio will work.

%macro TFSRev(filename);
    %let tfsver1=;
    %let tfsver2=;
    %let tfsver3=;
 
    /* The default path for the TF program tf.exe with Visual Studio 13. */
    %let TFRev=C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\tf.exe;
 
    filename tfver pipe "call ""&TFRev"" hist ""&filename"" ";
 
    data _null_;
        infile tfver;
        input;
        /* The output is in three different lines. */
        put _n_ _infile_;
        /* Puts the three different output lines in three diffent macrovariables called tfsver1, tfsver2 and tfsver3. */
        call symput('tfsver' !! put(_n_,1.), _infile_);
    run;
 
    /* Suppress output from SAS. */
    options nosource;
    %put Extracting TFS information from: &filename;
    %put;
    %put &tfsver1;
    %put &tfsver2;
    %put &tfsver3;
    option source;
%mend;

%TFSRev(C:\TEMP\sasprogram.sas);

%macro TFSRev(filename);

%let tfsver1=;

%let tfsver2=;

%let tfsver3=;

/* The default path for the TF program tf.exe with Visual Studio 13. */

%let TFRev=C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\tf.exe;

filename tfver pipe "call ""&TFRev"" hist ""&filename"" ";

data _null_;

infile tfver;

input;

/* The output is in three different lines. */

put _n_ _infile_;

/* Puts the three different output lines in three diffent macrovariables called tfsver1, tfsver2 and tfsver3. */

call symput('tfsver' !! put(_n_,1.), _infile_);

run;

/* Suppress output from SAS. */

options nosource;

%put Extracting TFS information from: &filename;

%put;

%put &tfsver1;

%put &tfsver2;

%put &tfsver3;

option source;

%mend;

%TFSRev(C:\TEMP\sasprogram.sas);

You can now use the three diffent macrovariables called tfsver1, tfsver2 and tfsver3 in your program.

Microsoft Visual Studio Team Foundation Server 2013 Power Tools might be needed.
https://marketplace.visualstudio.com/items?itemName=TFSPowerToolsTeam.MicrosoftVisualStudioTeamFoundationServer2013Power

You might also want to have a look at this blogpost regarding SVN.

SAS

Automatically analyzing and documenting SAS-code

March 28, 2018 Root Leave a comment

In SAS Enterprise Guide and SAS-DI you have the possibility to analyze the code for a SAS-program.

The picture below shows the Analyze Program option in SAS Enterprise Guide.

Analyzing the code should result in a conversion of the SAS-code to a SAS Enterprise Guide flow or SAS-DI flow. But none of these code analyzers are very good. Depending on the complexity of the SAS-code you put into the analyzer, they will leave you with a more or less successful conversion. And more times than not they will fail at doing the job.

But SAS comes with a procedure PROC SCAPROC that does a really good job at analyzing and documenting SAS-code.

Below is an example.

proc scaproc;
record '<PATH>' attr expandmacros;
run;
 
proc scaproc;
write;
run;

proc scaproc;

record '<PATH>' attr expandmacros;

run;

proc scaproc;

write;

run;

The links below gives you further descriptions of PROC SCAPROC and its options. There’s also a guide on how to do a graphical presentation of the result fra PROC SCAPROC.

Overview of the SCAPROC Procedure
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a003199745.htm

Program for parsing the output from PROC SCAPROC to create a data set with inputs and outputs.
http://support.sas.com/kb/58/047.html

Innovative Performance Improvements Through Automated Flowcharts In SAS
http://support.sas.com/resources/papers/proceedings16/11580-2016.pdf

Automatically create diagrams showing the structure and performance of your SAS code
http://support.sas.com/resources/papers/proceedings17/1104-2017.pdf

SAS

Uniqueness in data

March 8, 2018 Root Leave a comment

The SAS-macro below will tell you if a variable in a dataset is unique.

/********************************************************************************
Author        : 
Creation date : ddmmmyyy
Description   : Gets info about uniqness in a SAS-dataset.
Example       : %uniq(sashelp.class, name, print)
*********************************************************************************
Input
-----
&datset   : The dataset to test.
&variable : The variable to test for uniqueness.
&print    : If the output/result should be shown in a PROC PRINT.
*********************************************************************************
Output
------
freq_result          : Dataset sorted with doublets as first rows.
freq_result_doublets : Data containing only the doublets.
********************************************************************************/
%macro uniq(dataset, variable, print);
	proc freq data=&dataset.;
		tables &variable / noprint out=freq_result;
	run;

	proc sort data=freq_result;
		by descending count;
	run;

	data freq_result_doublets;
		set freq_result;
		where count gt 1;
	run;

	proc sql noprint;
		select count(*) into :doublets
		from freq_result_doublets
		where count gt 1
		;
	quit;

	%put --------------------------------------------------------------------------------------------;
	%put NUMBER OF DOUBLETS IN [%upcase(&dataset.)] FOR VARIABLE [%upcase(&variable.)]: &doublets.;
	%put --------------------------------------------------------------------------------------------;

	%if &doublets. eq 0 %then
	%do;
		%put !!!! NO DOUBLETS !!!;
	%put --------------------------------------------------------------------------------------------;
	%end;

        %if &print. ne %then
        %do;
             proc print data=freq_result;
             run;
        %end;
%mend;

/********************************************************************************

Author :

Creation date : ddmmmyyy

Description : Gets info about uniqness in a SAS-dataset.

Example : %uniq(sashelp.class, name, print)

*********************************************************************************

Input

-----

&datset : The dataset to test.

&variable : The variable to test for uniqueness.

&print : If the output/result should be shown in a PROC PRINT.

*********************************************************************************

Output

------

freq_result : Dataset sorted with doublets as first rows.

freq_result_doublets : Data containing only the doublets.

********************************************************************************/

%macro uniq(dataset, variable, print);

proc freq data=&dataset.;

tables &variable / noprint out=freq_result;

run;

proc sort data=freq_result;

by descending count;

run;

data freq_result_doublets;

set freq_result;

where count gt 1;

run;

proc sql noprint;

select count(*) into :doublets

from freq_result_doublets

where count gt 1

;

quit;

%put --------------------------------------------------------------------------------------------;

%put NUMBER OF DOUBLETS IN [%upcase(&dataset.)] FOR VARIABLE [%upcase(&variable.)]: &doublets.;

%put --------------------------------------------------------------------------------------------;

%if &doublets. eq 0 %then

%do;

%put !!!! NO DOUBLETS !!!;

%put --------------------------------------------------------------------------------------------;

%end;

%if &print. ne %then

%do;

proc print data=freq_result;

run;

%end;

%mend;

SAS

Directory for SAS-macros

March 7, 2018 Root Leave a comment

The code below will give you the path of all the directories containing macros in the SASAUTOS in your SAS-session.

%put %sysfunc(getoption(sasautos));
filename _all_ list;

%put %sysfunc(getoption(sasautos));

filename _all_ list;

Windows, Windows 8

Testing a port with PowerShell

January 21, 2018 Root Leave a comment

In newer versions of Windows you are not able to use the Telnet-command to test a port. Don’t worry. In PowerShell v. 4 and onwards you can use the command below.

Test-NetConnection <host> -port <port>

1	Test-NetConnection <host> -port <port>

Eg.

Test-NetConnection www.ryslander.com -port 80

1	Test-NetConnection www.ryslander.com -port 80

DB2, SSIS

SSIS and IBM DB2 – Don’t do it!

July 1, 2017 Root Leave a comment

SSIS is a product that should be indifferent to whatever RDBMS you use. That’s also true. It will work with every RDBMS through drivers ODBC, OLE DB, ADO.NET etc. BUT it works best with Microsofts SQL-server.

This blogpost will take a look at using IBM DB2 with SSIS. Below is a test using ODBC-driver for IBM DB2 from IBM, OLE DB for IBM DB2 from Microsoft and an ADO.NET-driver.

Transfer rate of data

Transferring 100.000 rows from one IBM DB2-server to another IBM DB2-server.

OLE DB-driver for IBM DB2 from Microsoft	6 minuts
ADO.NET-driver	30 seconds
ODBC-driver for IBM DB2 from IBM	5 seconds

As seen above the ODBC-driver for IBM DB2 from IBM is the best solution when it comes to transfer rate. The OLE DB-driver for IBM DB2 from Microsoft is a really poor choice. But using the ODBC-driver for DB2 from IBM is not the solution to everything!

Lookup-task in SSIS

As seen in the picture below it’s only possible to use an OLE DB-connection in the Lookup-task. The Lookup-task is really slow and it might be better doing it in SQL. Regarding a lot of the tasks in SSIS, they are really slow (see links below).

OLE DB Command

SSIS also contains a ‘OLE DB Command’-task and no ‘ODBC Command’-task.

Fast load/Bulk load

OLE DB
It’s NOT possible to use the fast load option with the OLE DB-driver when it comes to IBM DB2. It’s possible to choose the fastload option, but you will get the error below.

The reason why the OLE DB-driver is that slow is probably that it transfers one row at a time.

ODBC
It’s possible to use a batch/fast load load option in ODBC.

ADO.NET
It’s also possible to use a bulk/fast load option in ADO.NET.

Links

The links below is to different sources that can help you with SSIS.

SQL Server Integration Services Design Patterns
A book that describes different design pattern for SSIS.

Task Factory for SSIS from Pragmatic Works
A collection of optimized SSIS-tasks.

Cozyroc
A collection of optimized SSIS-tasks.

PragmaticWorks free online training videos
Free online training videos about SSIS (search for SSIS).

Pluralsight
Online training videos in SSIS (requires a paid subscription to Pluralsight).

Ryslander.com

Getting first megabytes of large file

SQL-statement to find lowest observation in dataset

Enterprise Password Managers

Info about SAS-datasets in the WORK-library

Microsoft Team Foundation Server (TFS) and SAS

Automatically analyzing and documenting SAS-code

Uniqueness in data

Directory for SAS-macros

Testing a port with PowerShell

SSIS and IBM DB2 – Don’t do it!

implementation, consolidation and innovation