/* Jump to: Chapter 7 Table of Contents Chapter 9 */
/*
*/
/* Jump to: Next Section */
DATA wide;
INPUT ID $ Measure1-Measure4;
CARDS;
A 11 12 13 14
B 21 22 23 24
;
PROC SUMMARY DATA=wide;
VAR Measure1-Measure4;
OUTPUT OUT=sums(DROP = _type_ _freq_)
SUM = Sum1-Sum4;
RUN;
DATA sums;
SET wide END=last;
ARRAY _measure{*} measure1-measure4;
ARRAY _sum{*} Sum1-Sum4;
KEEP sum1-sum4;
DO i = 1 TO 4;
_sum{i} + _measure{i};
end;
IF last THEN OUTPUT;
RUN;
PROC SQL;
CREATE TABLE sums AS
SELECT SUM(measure1) AS Sum1,
SUM(measure2) AS Sum2,
SUM(measure3) AS Sum3,
SUM(measure4) AS Sum4
FROM wide
;
QUIT;
%MACRO selectsums(maxindex=);
%DO n = 1 %TO &maxindex;
SUM(measure&n) as Sum&n
%IF &n NE &maxindex %THEN ,
;
%END;
%MEND selectsums;
PROC SQL;
CREATE TABLE sums AS
SELECT %selectsums(maxindex=4)
FROM wide
;
QUIT;
/* Jump to: Top of Section Top of Chapter */
/* Jump to: Next Section */
PROC SQL;
CREATE TABLE thirteens AS
SELECT name AS FName,
height FORMAT=6.1,
weight FORMAT=6.1
FROM sashelp.class
WHERE age=13
;
QUIT;
DATA _NULL_;
SET thirteens END=lastobs;
heightsum + height;
IF (lastobs) THEN
CALL SYMPUT('avgheight', PUT(heightsum / _N_, 4.1) );
RUN;
%put macro variable AVGHEIGHT: [&avgheight];
TITLE 'Heights of 13-Year-Olds';
FOOTNOTE "Average Height is &avgheight";
PROC PRINT DATA=thirteens;
ID fname;
VAR height;
RUN;
PROC SQL;
RESET NOPRINT;
SELECT PUT(MEAN(height),4.1)
INTO : avgheight
FROM thirteens
;
RESET PRINT;
%PUT macro variable AVGHEIGHT: [&avgheight];
TITLE 'Heights of 13-Year-Olds';
FOOTNOTE "Average Height is &avgheight";
SELECT fname, height
FROM thirteens
;
TITLE;
FOOTNOTE;
QUIT;
PROC SQL;
SELECT MAX(fname), MIN(fname)
FROM thirteens
;
QUIT;
PROC SQL;
SELECT MAX(fname), MIN(fname)
INTO : max_fname , : min_fname
FROM thirteens
;
QUIT;
%PUT macro variable MAX_FNAME: [&max_fname];
%PUT macro variable MIN_FNAME: [&min_fname];
PROC SQL;
SELECT MAX(weight), MIN(weight)
INTO : max_weight
FROM thirteens
;
QUIT;
%PUT macro variable MAX_WEIGHT: [&max_weight];
%PUT macro variable MIN_WEIGHT: [&min_weight];
PROC SQL;
SELECT MAX(height)
INTO : max_height, : min_height
FROM thirteens
;
QUIT;
%PUT macro variable MAX_HEIGHT: [&max_height];
%PUT macro variable MIN_HEIGHT: [&min_height];
PROC SQL;
SELECT fname
FROM thirteens
;
QUIT;
DATA _NULL_;
SET thirteens;
CALL SYMPUT('fname'||COMPRESS(PUT(_N_,4.) ),fname);
RUN;
%PUT macro variables: [&fname1,&fname2,&fname3];
PROC SQL;
SELECT fname
INTO : fname
FROM thirteens
;
QUIT;
%PUT macro variable FNAME: [&fname];
PROC SQL;
SELECT fname
INTO : fname1 THROUGH : fname3
FROM thirteens
;
QUIT;
%PUT macro variables: [&fname1,&fname2,&fname3];
PROC SQL;
SELECT fname
INTO : fname1 THROUGH : fname999
FROM thirteens
;
QUIT;
%PUT macro variable FNAME4: [&fname4];
DATA _NULL_;
SET thirteens END=done;
LENGTH fname_string $ 9999;
RETAIN fname_string;
fname_string = CATX(' / ',fname_string,fname);
IF done THEN CALL SYMPUT('fnames',TRIM(fname_string) );
RUN;
%PUT FNAMES: [&fnames];
PROC SQL;
SELECT fname
INTO : fnames SEPARATED BY ' / '
FROM thirteens
;
QUIT;
%PUT FNAMES: [&fnames];
PROC DATASETS;
MODIFY wide;
RENAME Measure1 = Estimated
Measure2 = Net
Measure3 = Gross
Measure4 = Adjusted
;
RUN;
QUIT;
PROC SQL;
SELECT name, type
FROM DICTIONARY.COLUMNS
WHERE libname = 'WORK' AND
memname = 'WIDE'
;
QUIT;
PROC SQL;
SELECT 'sum(' || TRIM(name) || ') as Sum_' || name
INTO : selections SEPARATED BY ', '
FROM DICTIONARY.COLUMNS
WHERE libname = 'WORK' AND
memname = 'WIDE' AND
type = 'num'
;
QUIT;
%PUT &selections;
PROC SQL;
CREATE TABLE sums AS
SELECT &selections
FROM wide
;
QUIT;
PROC SQL;
SELECT MAX(height)
FROM thirteens
;
QUIT;
%PUT SQL Return Code is &sqlrc;
PROC SQL;
CREATE TABLE maxmin AS
SELECT MAX(height) AS m_height,
MIN(height) AS m_height
FROM thirteens
;
QUIT;
%PUT SQL Return Code is &sqlrc;
PROC SQL;
*
SELECT fname
FROM thirteens
UNION
SELECT height
FROM thirteens
;
QUIT;
%PUT SQL Return Code is &sqlrc;
PROC SQL;
*
SELECT *,
(SELECT fname
FROM thirteens
WHERE height>60)
FROM thirteens
;
QUIT;
%PUT SQL Return Code is &sqlrc;
PROC SQL;
SELECT student.fname,
classmate.fname
FROM thirteens AS student
JOIN
thirteens AS classmate
ON student.fname NE classmate.fname
WHERE student.height>60 and classmate.weight<90
;
QUIT;
%PUT macro variable SQLOBS: [&sqlobs];
/* Jump to: Top of Section Top of Chapter */