* HOW Productive SQL Code.sas; * Example programs from Quick Results with Proc SQL e-Guide; * http://www.SASSavvy.com; * Sunil Gupta; * Gupta Programming; * September 15, 2011; dm log 'clear'; dm output 'clear'; *ods rtf file='F:\HOW\HOW_Productive_SQL_code.rtf'; title1 "Productive PROC SQL HOW: Sample data set"; proc contents data=sashelp.class; run; proc sort data=sashelp.class out=class; by sex; run; proc print data=class; run; *******************************************************************************************; title1 "HOW Example 1: Basic Example with all four components"; title2 "Four components: A. Columns (), B. Joins (), C. Condition (), D. Sort ()"; title3 "Which components are required?"; * Can you list seven benefits for using PROC SQL? - A. Create View, Table B. Create, C. Alter, D. Update, E. Insert, F. Delete; proc sql; select name from sashelp.class where sex = 'F' order by name; quit; *******************************************************************************************; title1 "HOW Example 2a: Select Columns (name, sex)"; * Can you list four selection options? a. ',', b. label, c. '*', d. distinct; proc sql; select name, sex from sashelp.class where sex = 'F' order by name; quit; title1 "HOW Example 2b: Select Columns (Add Attributes - label, format and length)"; proc sql; select name label = 'My label' format = $10. length = 10 from sashelp.class where sex = 'F' order by name; quit; title1 "HOW Example 2c: Select All Columns (*)"; proc sql; select * from sashelp.class where sex = 'F' order by name; quit; title1 "HOW Example 2d: Select Columns (distinct)"; * Why is the value of 'F' repeated and how can you correct to display only once?, How is distinct most often used?; * How can you prevent the warning in the log?; proc sql; select distinct sex from sashelp.class where sex = 'F' order by name; quit; title1 "HOW Example 2e: Selecting Columns (distinct) without order by clause"; * Why is the value of 'F' repeated and how can you correct to display only once?, How is distinct most often used?; proc sql; select distinct sex from sashelp.class where sex = 'F'; quit; *******************************************************************************************; title1 "HOW Example 3a: Creating Column using Functions int((age+150)/10) as myage"; * Can you list eight create options? a. int(), b. max(), c. sum(), d. 'constant', e.character expression, f. select-case; proc sql; select age, int((age+150)/10) as myage length=8 format=3. from sashelp.class; quit; title1 "HOW Example 3b: Creating Column using Functions max(height, weight) as maxval"; proc sql; select height, weight, max(height, weight) as maxval length=8 format=3. from sashelp.class; quit; title1 "HOW Example 3c1: Creating Column using Summary Functions((weight/sum(weight))*100) as wpercnt"; proc sql; select weight, ((weight/sum(weight))*100) as wpercnt length=8 format=4.1 from sashelp.class; quit; title1 "HOW Example 3c2: Creating Column Group by Sex (sum(weight) as sum_weight (weight/sum(weight))*100) as wpercnt"; proc sql; select sex, weight, sum(weight) as sum_weight length=8 format=4.0, ((weight/sum(weight))*100) as wpercnt length=8 format=4.1 from sashelp.class group by sex; quit; title1 "HOW Example 3c3: Creating Column Group by Sex (sum(weight) as sum_weight"; proc sql; select sex, sum(weight) as sum_weight length=8 format=4.0 from sashelp.class group by sex; quit; title1 "HOW Example 3c4: Creating Column Group by Sex for each name count(sex) as gender_cnt"; * How can you display the count by sex for each name and order by sex?; proc sql; select sex, name, count(sex) as gender_cnt length=8 format=4.0 from sashelp.class group by sex order by sex; quit; title1 "HOW Example 3d: Creating Column using 'my constant' as myname"; * Why is my constant repeated 19 times? Total number of observations How can you change to display only once? distinct; proc sql; select 'my constant' as myname length=15 from sashelp.class; quit; title1 "HOW Example 3e: Creating Column using character expression ' name || ',' || sex as namesex"; * How can you clean up this variable? trim(left(name)); proc sql; select name || "," || sex as namesex length=35 from sashelp.class; quit; title1 "HOW Example 3f: Creating Column using select-case when to create depthead"; * What clause can be added to confirm the mapping of age? - order by age; proc sql; select age, case when age > 0 and age < 13 then 1 when age between 13 and 15 then 2 when age > 15 then 3 else . end as agegrp length=8 from sashelp.class; quit; title1 "HOW Example 3g: Creating Column using select-case when to create depthead"; * What clause can be added to confirm the mapping of age? - order by age; proc sql; select sex, case sex when 'M' then 1 when 'F' then 2 else . end as sexgrp length=8 from sashelp.class; quit; title1 "HOW Example 3h: Creating Columns using where condition in summary function"; proc sql; select sum(sex=’M’) as nmale length = 4, sum(sex=’F’) as nfemale length = 4 from sashelp.class; quit; *******************************************************************************************; title1 "HOW Example 4a: Subsetting tables using calculated depthead variable"; * Can you list the three options for subsetting tables? a. direct variable, b. calculated variable, c. function; proc sql; select age, case when age > 0 and age < 13 then 1 when age between 13 and 15 then 2 when age > 15 then 3 else . end as agegrp length = 4 from sashelp.class where calculated agegrp = 3; quit; title1 "HOW Example 4b: Subsetting tables using Function index()"; proc sql; select name, sex from sashelp.class where index(name, 'J') > 0; quit; *******************************************************************************************; title1 "HOW Example 5a: Using Subquery Conditions resulting in one row"; title2 'Select by sex, sex and weight, weight greater than the overall average weight'; title3 'Three part approach: subquery results, population, confirm subset'; proc sql; create table mean_wgt as select avg(weight) as m_wgt from sashelp.class; select m_wgt from mean_wgt; quit; proc sql; Select sex, weight from sashelp.class order by sex, weight; quit; proc sql; Select sex, weight from sashelp.class having weight > (select m_wgt from mean_wgt); quit; title1 "HOW Example 5b: Using Subquery Conditions resulting in multiple rows"; title2 'Select by sex, sex and age, for non-matching female ages'; title3 'Three part approach: subquery results, population, confirm subset'; proc sql; select distinct age from sashelp.class where sex = "F" order by age; quit; proc sql; Select sex, age from sashelp.class order by sex; quit; proc sql; Select sex, age from sashelp.class having age ~in (select distinct age from sashelp.class where sex = "F") ; quit; *******************************************************************************************; title1 "HOW Example 6a: Creating macro variable resulting in one row"; title2 'Count of males'; title3 'Two part approach: value to save, macro variable name'; proc sql; select count(sex) as gender_cnt from sashelp.class where sex = 'M'; quit; proc sql; select count(sex) as gender_cnt into :male_cnt from sashelp.class where sex = 'M'; quit; %put 'Number of Males = ' &male_cnt; title1 "HOW Example 6b: Creating macro variables resulting in multiple rows"; title2 'Names of males'; title3 'Two part approach: value to save, macro variable names'; * How can you add a space between the names instead of a comma?; proc sql; select name as male from sashelp.class where sex = 'M'; quit; proc sql; select name as male into :male_name separated by ', ' from sashelp.class where sex = 'M'; quit; %put 'Names of Males = ' &male_name; *ods rtf close;