SAS PROC SQL -- merging and calculation

proc sql ;
create table new as
select a._Material_Substance_Use_Abuse,
(a._1_Yes+a._2_No) as total_n, a._1_Yes as Yes_n, a._2_No as No_n,
a._3_Section_not_available as No_no_section,
(a._1_Yes/(a._1_Yes+a._2_No)) as YES_percentage_wo_sec,
(a._1_Yes/(a._1_Yes+a._2_No+a._3_Section_not_available)) as YES_percentage_w_sec
from Eye_twoT1 AS a
;

SAS PROC SQL

Get the number of distinct values.

proc sql;
create table temp2 as select *,
count(distinct ID) as duplic
from final.cleaned
group by Case_num;
run;

SQL basics

 

I need quotes when specifying a date value.

SELECT *
FROM purchases
WHERE purchased_at <= "2018-11-01";

 

Pick up rows whose values contain "pudding":

SELECT *
FROM purchases
WHERE name like "%pudding%";

 

You can use NOT:

SELECT *
FROM purchases
WHERE NOT character_name="Ken";

 

Another example of NOT:

SELECT *
FROM purchases
WHERE NOT name like "%pudding%";

 

 

SELECT *
FROM purchases
WHERE price IS NOT NULL;

 

Ordering observations

SELECT *
FROM purchases
WHERE character_name = "Ken"
ORDER BY price DESC;

 

NOT

SELECT *
FROM purchases
WHERE NOT character_name = "Ken";

 

SELECT *
FROM purchases
WHERE price IS NULL;

 

SELECT *
FROM purchases
ORDER BY price DESC
LIMIT 5;

 

Counting the row of observations

This counts  non-missing values.

SELECT COUNT(name)
FROM purchases;

This counts the number of rows.

SELECT COUNT(*)
FROM purchases;

Using the where statement:

SELECT COUNT(*)
FROM purchases
WHERE character_name="Ken"
;

Picking up the observation whose value is a maximum value

SELECT name, max(price)
FROM purchases
WHERE character_name="Ken"
;

GET THE SUM PER GROUP

SELECT SUM(price), purchased_at
FROM purchases
GROUP BY purchased_at
;

 

SELECT COUNT(*), purchased_at
FROM purchases
GROUP BY purchased_at;

 

SELECT SUM(price), purchased_at
FROM purchases
WHERE character_name="Ken"
GROUP BY purchased_at

;

 

SELECT SUM(price), purchased_at
FROM purchases
GROUP BY purchased_at
HAVING sum(price) > 20;

 

This picks up cases with a condition (in this case, whoever had a higher score than the guy Will).

SELECT name
FROM players
WHERE goals > (
-- Write an SQL statement below to get Will's score
SELECT goals
FROM players
WHERE name = "Will"
)
;

 

 

SELECT name,goals
FROM players
WHERE goals > (
SELECT AVG(goals)
FROM players
)
;

 

Using AS:

SELECT name AS "180 cm or taller"
FROM players
WHERE height >= 180
;

 

SELECT SUM(goals) AS "total team score"
FROM players
;

 

SELECT *
FROM countries
WHERE rank < (
SELECT rank
FROM countries
WHERE name="Japan"
)
;

 

Merging two tables:

SELECT *
FROM players
-- Add a name to the combined table
JOIN countries
-- Add a join condition
ON players.country_id = countries.id
;

 

 

SELECT players.name, countries.name
FROM players
JOIN countries
ON players.country_id = countries.id
;

 

SELECT countries.name, SUM(goals)
FROM players
JOIN countries
ON players.country_id = countries.id
GROUP BY countries.name
;

 

 

SELECT *
FROM players
JOIN teams
ON players.previous_team_id = teams.id
;

 

SELECT players.name AS "player name", teams.name AS "team (last year)"
FROM players
JOIN teams
ON players.previous_team_id = teams.id
;

SELECT *
FROM players
LEFT JOIN teams
ON players.previous_team_id =teams.id
;

 

SELECT players.name AS "player name", teams.name AS "team (last year)"
FROM players
LEFT JOIN teams
ON players.previous_team_id = teams.id
;

SELECT *
FROM players
JOIN countries
ON players.country_id = countries.id
LEFT JOIN teams
ON players.previous_team_id = teams.id
;

 

SELECT players.name AS "Player name", players.height AS "height"
FROM players
WHERE height > (
SELECT AVG(height)
FROM players
)

 

select name, price
from items
order by price desc
;

 

-- get all rows that contain the string "shirt"
SELECT *
FROM items
WHERE name like "%shirt%"
;

 

SELECT name, price, MAX(price - cost)
FROM items;

 

SELECT name, price
FROM items
WHERE price > (
SELECT price
FROM items
WHERE name = "grey hoodie"
);

MySQL Basic codes

DROP TABLE data._01_abc;

CREATE TABLE data._01_abc (

var1(22) NULL,

var2 int NULL,
var3 char(2) NULL);

LOAD DATA INFILE "sql.csv"
INTO TABLE data._01_abc
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

SQL: Create Table and Bulk Insert

 

CREATE TABLE DFC3
(
xxx VARCHAR(30),
xxx INT ,
xxx VARCHAR(30),
xxx VARCHAR(19),
xxx VARCHAR(6),
xxx INT,
xxx INT ,
xxx VARCHAR(19),
xxx INT ,
x  INT ,
xxx VARCHAR(33),
xx INT ,
x INT ,

)

GO
BULK
INSERT DFC3
FROM 'xxxx.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
SELECT TOP 10 *
FROM DFC3
GO
SELECT *
FROM DFC3
GO