SAS to SQL. How to do nway with SQL

Recently I have been translating SAS-code to SQL. Old SAS was pretty new for me. Some things was pretty easy to understand. Some things about SAS code are somewhat cryptic. For example SAS will make worktable "prices_per_productype" with this command:

proc summary nway missing data = invoice;
class id_corporation id_productgroup ;
var price tax;
output out=prices_per_productype (drop=_type_ _freq_) sum= ;
run;

When I translate it to SQL it looks like this:

SELECT
id_corporation, 
id_productgroup,
sum(price) as price, 
sum(tax) as tax
FROM invoice
GROUP BY id_corporation, id_productgroup 

If data looks like this before processing:

id_corporation	id_ productgroup	price	tax
12	6	10	10
12	34	350	35
12	34	400	40
12	34	10	1
14	17	900	90
14	48	50	5

 

 Data will look like this after processing (in both cases):

id_corporation	id_ productgroup	price	tax
12	6	10	10
12	34	760	76
14	17	900	90
14	48	50	5

                            

How to get person's birthdate from Finnish social security number with SQL-query

 Finnish social security number is datemask ddmmYYxAAbc

• dd = date with two numbers.
• mm = month with two numbers.
• YY = year with two numbers.
• x = "delimeter" character. It can be '+','-' or 'A'. It presents century.

• '+' = 1800
• '-' = 1900
• 'A' = 2000
• AAbc = varying numbers and caharacters for id purposes including cheksum. We are not interested in about it this time.

SQL-query:

DECLARE @sSecurityId char(11);

SET @sSecurityId = N'221177-123Z';  

select CONVERT(date, CASE SUBSTRING(@sSecurityId, 7, 1)
    WHEN '-' THEN '19'
    WHEN 'A' THEN '20'
    WHEN '+' THEN '18' 
END + SUBSTRING(@sSecurityId, 5, 2) + SUBSTRING(@sSecurityId, 3, 2) + SUBSTRING(@sSecurityId, 1, 2)) as birthdate

Result: 1977-22-11

Keywords for Finnish developers:

Kuinka muuttaa suomalainen sosiaaliturvatunnus, eli sotu, päivämääräksi tai päiväksi SQL-haun avulla.

ODBC connection error

This hint is maybe useful to you if your ODBC connection is configured with server name and not with IP number.

One day there was an error message in ETL log.

Connection failed:

SQLState: ’01000’
SQL Server Error: 11001
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOPen (Connect())
Connection failed:
SQLState: ’08001’
SQL Server Error: 6
[Microsoft][ODBC SQL Server Driver][DBNETLIB]Specifed SQL server not found.

 ETL was using ODBC drivers. Connections were established with ODBC Data Source Administrator.

When I tried configurations of faulty connections with ODBC Data Source Administrator I got same errors as above again.

 First I tried hints from Bobcares site with no luck.

Then I used command line and PING-commad (PING [server_name]). Ping went through just fine.

I tried to reconfigure connection with ODBC Data Source Administrator again. I made new connection actually. I copied settings from faulty ODBC setting but replaced server name with IP-number, which I got response from PING command. This time ODBC Data Source Administrator give SUCCESS from connection test! I renamed faulty connection with “_old” ending and renamed new connection with previous name.

ETL-process went through with any errors.

What was wrong? I don’t know. There was other ODBC connections to different databases with the same server and they were working just fine with server name.

But hey! Connection works!