/* Jump to: Chapter 8 Table of Contents Chapter 10 */
/*
*/
/* Jump to: Next Section */
DATA simple;
DO Measure = 1 to 3; OUTPUT; END;
RUN;
DATA simple_but_labeled;
SET simple;
LABEL measure = 'Level reported after calibration';
RUN;
DATA simple;
DO Measure = 1 to 3; OUTPUT; END;
RUN;
DATA simple;
SET simple;
LABEL measure = 'Level reported after calibration';
RUN;
DATA simple;
DO Measure = 1 TO 3; OUTPUT; END;
RUN;
PROC DATASETS;
MODIFY simple;
LABEL measure = 'Level reported after calibration';
RUN;
QUIT;
/* Jump to: Top of Section Top of Chapter */
/* Jump to: Next Section */
LIBNAME demolib "c:\temp\demolib";
%MACRO refresh_example;
PROC DATASETS LIBRARY=demolib KILL;
RUN;
PROC SQL;
CREATE TABLE demolib.fifteenups AS
SELECT name AS FName, sex, age, height, weight
FROM sashelp.class
WHERE age GE 15
;
QUIT;
%MEND refresh_example;
DATA insertions;
INFORMAT sex $1.;
INPUT FName $ Sex Height Weight Age;
CARDS;
Adam M 68 130 15
Joan F 64 120 16
;
DATA deletions;
INPUT FName $;
CARDS;
Mary
Ronald
;
DATA corrections;
INPUT FName $ Height Weight;
CARDS;
Janet 64 .
William . 118
;
/* Jump to: Top of Section Top of Chapter */
/* Jump to: Next Section */
OPTIONS NOREPLACE;
DATA demolib.fifteenups;
SET demolib.fifteenups;
FORMAT height weight 3.;
RUN;
OPTIONS NOREPLACE;
PROC DATASETS LIBRARY=demolib;
MODIFY fifteenups;
FORMAT height weight 3.;
RUN;
QUIT;
OPTIONS REPLACE;
PROC SQL;
CREATE TABLE demolib.fifteenups AS
SELECT *
FROM demolib.fifteenups;
QUIT;
%refresh_example
PROC SQL;
CREATE TABLE not_empty AS
SELECT SUBSTR(fname,1,1) LENGTH=1 AS Initial,
sex,
height
FROM demolib.fifteenups
WHERE age=15
;
QUIT;
PROC SQL;
CREATE TABLE empty AS
SELECT SUBSTR(fname,1,1) LENGTH=1 AS Initial,
sex,
height
FROM demolib.fifteenups
WHERE age=17
;
QUIT;
PROC SQL;
CREATE TABLE empty AS
SELECT '' LENGTH=1 AS Initial,
sex,
height
FROM demolib.fifteenups
WHERE 0
;
QUIT;
DATA empty;
STOP;
LENGTH Initial $ 1;
SET demolib.fifteenups;
KEEP initial sex height;
RUN;
PROC SQL;
CREATE TABLE fifteenups_clone
LIKE demolib.fifteenups
;
QUIT;
DATA fifteenups_clone;
STOP;
SET demolib.fifteenups;
RUN;
PROC SQL;
CREATE TABLE from_scratch
(
First CHARACTER(10)
LABEL='Label for 1st column',
Second NUMERIC
FORMAT=7.2
)
;
QUIT;
DATA from_scratch;
ATTRIB First LENGTH = $ 10
LABEL='Label for 1st column'
Second FORMAT=7.2;
STOP;
RUN;
PROC SQL;
DESCRIBE TABLE demolib.fifteenups;
QUIT;
PROC DATASETS LIBRARY=demolib;
DELETE fifteenups;
RUN;
%refresh_example
PROC SQL;
DROP TABLE demolib.fifteenups
;
QUIT;
/* Jump to: Top of Section Top of Chapter */
/* Jump to: Next Section */
%refresh_example
PROC SQL;
INSERT INTO demolib.fifteenups
SET fname = "Adam" ,
weight = 130 ,
sex = "M" ,
height = 68 ,
age = 15
SET fname = "Joan" ,
sex = "F" ,
age = 16 ,
height = 64 ,
weight = 120
;
QUIT;
%refresh_example
DATA demolib.fifteenups;
fname = "Adam" ;
sex = "M" ;
age = 15 ;
height = 68 ;
weight = 130 ;
OUTPUT;
fname = "Joan" ;
sex = "F" ;
age = 16 ;
height = 64 ;
weight = 120 ;
OUTPUT;
STOP;
MODIFY demolib.fifteenups;
RUN;
%refresh_example
PROC SQL;
INSERT INTO demolib.fifteenups
VALUES ("Adam", "M", 15, 68, 130)
VALUES ("Joan", "F", 16, 64, 120)
;
QUIT;
%refresh_example
PROC SQL;
INSERT INTO demolib.fifteenups
(fname, sex, age, height, weight)
VALUES ("Adam", "M", 15, 68, 130)
VALUES ("Joan", "F", 16, 64, 120)
;
QUIT;
%refresh_example
PROC SQL;
INSERT INTO demolib.fifteenups
SELECT *
FROM insertions
;
QUIT;
%refresh_example
PROC SQL;
INSERT INTO demolib.fifteenups
(age, height, fname, sex, weight)
SELECT age, height, fname, sex, weight
FROM insertions
;
QUIT;
%refresh_example
PROC APPEND BASE=demolib.fifteenups DATA=insertions;
RUN;
%refresh_example
DATA demolib.fifteenups;
MODIFY demolib.fifteenups;
WHERE fname IN ('Mary','Ronald');
REMOVE;
RUN;
%refresh_example
PROC SQL;
DELETE FROM demolib.fifteenups
WHERE fname IN ('Mary','Ronald')
;
QUIT;
%refresh_example
DATA demolib.fifteenups;
MODIFY demolib.fifteenups deletions(IN=dropit);
BY fname;
IF dropit THEN REMOVE;
RUN;
%refresh_example
PROC SQL;
DELETE FROM demolib.fifteenups
WHERE fname IN (SELECT fname FROM deletions)
;
QUIT;
%refresh_example
DATA demolib.fifteenups;
MODIFY demolib.fifteenups;
IF fname='Janet' THEN DO;
height = 64;
REPLACE;
END;
IF fname='William' THEN DO;
weight = 118;
REPLACE;
END;
RUN;
%refresh_example
PROC SQL;
UPDATE demolib.fifteenups
SET height = 64
WHERE fname='Janet'
;
UPDATE demolib.fifteenups
SET weight = 118
WHERE fname='William'
;
QUIT;
%refresh_example
DATA demolib.fifteenups;
MODIFY demolib.fifteenups
corrections;
BY fname;
RUN;
%refresh_example
PROC SQL;
UPDATE demolib.fifteenups AS main
SET height = COALESCE( (SELECT sub.height
FROM corrections AS sub
WHERE main.fname=sub.fname),
height),
weight = COALESCE( (SELECT sub.weight
FROM corrections AS sub
WHERE main.fname=sub.fname),
weight)
;
QUIT;
%refresh_example
PROC SQL;
UPDATE demolib.fifteenups AS main
SET height = COALESCE( (SELECT sub.height
FROM corrections AS sub
WHERE main.fname=sub.fname),
height),
weight = COALESCE( (SELECT sub.weight
FROM corrections AS sub
WHERE main.fname=sub.fname),
weight)
WHERE fname IN (SELECT fname FROM corrections)
;
QUIT;
/* Jump to: Top of Section Top of Chapter */
/* Jump to: Next Section */
%refresh_example
PROC DATASETS LIBRARY=demolib;
MODIFY fifteenups;
FORMAT height 6.2;
LABEL height = 'Height in Inches';
RUN;
QUIT;
%refresh_example
PROC SQL;
ALTER TABLE demolib.fifteenups
MODIFY height FORMAT = 6.2
LABEL = 'Height in Inches'
;
QUIT;
/* Jump to: Top of Section Top of Chapter */
/* Jump to: Next Section */
%refresh_example
PROC SQL;
ALTER TABLE demolib.fifteenups
MODIFY fname CHAR (12)
DROP age, height, weight
ADD DoB DATE LABEL='Date of Birth'
;
QUIT;
%refresh_example
OPTIONS NOREPLACE;
PROC SQL;
*
ALTER TABLE demolib.fifteenups
MODIFY fname CHAR (12)
DROP age, height, weight
ADD DoB DATE LABEL='Date of Birth'
;
QUIT;
%refresh_example
OPTIONS REPLACE;
DATA demolib.fifteenups;
LENGTH FName $ 12;
SET demolib.fifteenups(DROP = age height weight);
ATTRIB DoB LABEL = 'Date of Birth'
FORMAT = date.
INFORMAT = date.;
RUN;
/* Jump to: Top of Section Top of Chapter */
/* Jump to: Next Section */
%refresh_example
PROC DATASETS LIBRARY=demolib;
MODIFY fifteenups;
INDEX CREATE age;
RUN;
QUIT;
%refresh_example
PROC SQL;
CREATE INDEX age
ON demolib.fifteenups
;
QUIT;
PROC PRINT DATA=demolib.fifteenups;
RUN;
PROC PRINT DATA=demolib.fifteenups;
BY age;
RUN;
DATA newtable( INDEX=(age) );
SET demolib.fifteenups;
RUN;
PROC SQL;
CREATE TABLE newtable( INDEX=(age) ) AS
SELECT *
FROM demolib.fifteenups
;
QUIT;
%refresh_example
PROC DATASETS LIBRARY=demolib;
MODIFY fifteenups;
INDEX CREATE age;
INDEX CREATE sex;
RUN;
MODIFY fifteenups;
INDEX DELETE age;
RUN;
QUIT;
PROC SQL;
CREATE INDEX age
ON demolib.fifteenups
;
DROP INDEX age
FROM demolib.fifteenups
;
QUIT;
PROC CONTENTS DATA=demolib.fifteenups;
RUN;
PROC SQL;
DESCRIBE TABLE demolib.fifteenups;
QUIT;
PROC DATASETS LIBRARY=demolib;
MODIFY fifteenups;
IC CREATE norepeats = UNIQUE(fname);
RUN;
QUIT;
PROC CONTENTS DATA=demolib.fifteenups;
RUN;
DATA demolib.fifteenups;
fname = 'Ronald';
OUTPUT;
STOP;
MODIFY demolib.fifteenups;
RUN;
PROC DATASETS LIBRARY=demolib;
MODIFY fifteenups;
IC DELETE norepeats;
RUN;
QUIT;
%refresh_example
PROC SQL;
ALTER TABLE demolib.fifteenups
ADD CONSTRAINT norepeats DISTINCT(fname)
;
QUIT;
PROC SQL;
DESCRIBE TABLE CONSTRAINTS demolib.fifteenups;
QUIT;
PROC SQL;
*
INSERT INTO demolib.fifteenups
SET fname = "Ronald"
;
QUIT;
PROC SQL;
ALTER TABLE demolib.fifteenups
DROP CONSTRAINT norepeats
;
QUIT;
PROC SQL;
CREATE TABLE AnotherTable
( SomeColumn character(20),
CONSTRAINT norepeats UNIQUE(SomeColumn),
AnotherColumn numeric
)
;
QUIT;
%refresh_example
PROC SQL;
*
ALTER TABLE demolib.fifteenups
ADD CONSTRAINT diff_age UNIQUE(age)
;
QUIT;
%refresh_example
PROC DATASETS LIBRARY=demolib;
AUDIT fifteenups;
INITIATE;
RUN;
QUIT;
DATA demolib.fifteenups;
fname = 'Ezra';
OUTPUT;
STOP;
MODIFY demolib.fifteenups;
RUN;
PROC PRINT DATA=demolib.fifteenups;
VAR fname;
RUN;
PROC PRINT DATA=demolib.fifteenups(TYPE=AUDIT);
VAR fname _atdatetime_;
FORMAT _atdatetime_ tod.;
RUN;
PROC SQL;
INSERT INTO demolib.fifteenups
SET fname = 'Nicole'
SET fname = 'Matthew'
;
QUIT;
PROC DATASETS LIBRARY=demolib;
AUDIT fifteenups;
TERMINATE;
RUN;
QUIT;
%refresh_example
DATA GDS_Demo(GENMAX=4);
SET demolib.fifteenups;
RUN;
DATA GDS_Demo;
SET demolib.fifteenups;
WHERE age=15;
RUN;
DATA GDS_Demo;
SET GDS_Demo;
KEEP fname sex age;
RUN;
PROC PRINT DATA=GDS_Demo(GENNUM=-2);
RUN;
PROC PRINT DATA=GDS_Demo(GENNUM=-1);
RUN;
PROC PRINT DATA=GDS_Demo;
RUN;
PROC SQL;
CREATE TABLE GDS_Demo (GENMAX=4) AS
SELECT *
FROM demolib.fifteenups
;
QUIT;
PROC SQL;
CREATE TABLE GDS_Demo AS
SELECT *
FROM demolib.fifteenups
WHERE age=15
;
QUIT;
PROC SQL;
CREATE TABLE GDS_Demo AS
SELECT fname, sex, age
FROM GDS_Demo
;
QUIT;
/* Jump to: Top of Section Top of Chapter */