20210307

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

                            


No comments:

Post a Comment

When scheduled BAT-files cascades

Server was acting slowly. What have happened? When I opened Task Manager. There were thousands of cmd-programs open. I noticed there were so...