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.