Лекция 6. Программирование на языке SAS. SQL (1185379)

Просмтор этого файла доступен только зарегистрированным пользователям. Но у нас супер быстрая регистрация: достаточно только электронной почты!

Текст из файла

Программирование на языке SASЛекция 6 (SQL)Авторы материалов:Звежинский Дмитрий (SAS Russia/CIS), dmitry.zvezhinsky@sas.comПетровский Михаил (ВМК МГУ, SAS Russia/CIS), michael@cs.msu.su1Процедура SQL• В SAS можно управлять структурой наборов данных с помощью языказапроса SQL.• SQL и шаг DATA дополняют друг друга.• Справка: SAS® 9.3 SQL Procedure User's Guide• Совместимость:–PROC SQL follows most of the guidelines set by the American National Standards Institute (ANSI) in its implementation ofSQL. However, it is not fully compliant with the current ANSI standard for SQL. The SQL research project at SAS has focusedprimarily on the expressive power of SQL as a query language. Consequently, some of the database features of SQL havenot yet been implemented in PROC SQL.• Синтаксис:• QUIT (вместо RUN) указывает на конец выражения SQL• Соответсвие реляционных таблий и SAS dataset:–––––Таблица – набор данныхСтрока – наблюдениеКолонка – перепеннаяNull - missingИндесы и view функционируют одиноково• Ограничения:– Нет обработки транзакций и определения прав доступа2Сравнение SAS SQL с шагом данных• Достоинства шага данных по сравнению с SQL:– Гибкие возможности по созданию сложно структурированныхданных и сложных расчетов «на лету»– Возможность создания нескольких наборов на одном шаге– Более глубокое взаимодействие со средой SAS– На порядок эффективнее выполение, если прогармму пишетквалифицированный программист• Недостатки шага данных по сравнению с SQL (многие изних нивелируются за счет применения процедур):– Язык шага даных требует как правило больше времени наизучение и написание кода чем SQL– Не всегда удобно и прозрачно работать с объединением наборовданных и расчетом агрегатов по ним– Нет «красивой» математической модели как реляционная алгебраи реляционной исчисление3Создание наборов данных с помощью SQL•Select выводит результаты SQL-запроса в отчет:proc sql;select * from sashelp.cars;quit;•Create table создаёт набор данных на основе другого набора:proc sql;create table table1 asselect t1.order_id, t1.customer_id, t1.order_typefrom ecsql1.qtr1_2007 as t1where t1.order_type=2;quit;Избранные опции к proc sql:proc sql outobs=10;WARNING: Statement terminated early due to OUTOBS=10 option.proc sql inobs=10;19select * from ecsql1.qtr1_2007;WARNING: Only 10 records were read from ECSQL1.QTR1_2007 due to INOBS= option.proc sql noexec;NOTE: Statement not executed due to NOEXEC option.proc sql _method _tree;(информация от планировщика, официально не документировано, см.

Paper CS-11The SQL Optimizer Project: _Method and _Tree in SAS®9.1, Russ Lavery)4Создание наборов данных с помощью SQL• Создание набора заданной структуры с индексом и view, атакже «ручное» заполнение:• Но через datalines на шаге данных удобнее:5Модификация структуры набора данных• На SQL проще (через alter table)• На SAS либо с помощью шага данных (можно и с помощьюпроцедур)6Объединение таблиц• «По горизонтали» (аналог merge)proc sql;select * from ecsql1.qtr1_2007 as t1, ecsql1.customer ast2where t1.customer_id = t2.customer_id;quit;proc sql;select * from ecsql1.qtr1_2007 as t1 joinecsql1.customer as t2on t1.customer_id = t2.customer_id;quit;• «По вертикали», причем в новой таблице будут все переменные извходящих таблиц (аналог set ecsql1.qtr1_2007 ecsql1.qtr2_2007)proc sql;create table qtr12 asselect * from ecsql1.qtr1_2007outer union corrselect * from ecsql1.qtr2_2007;quit;7Использование функций SAS• В SQL-запросах можно использовать встроенные и пользовательскиефункции.proc fcmp outlib=work.fun1.fun1;function myfun(id) $ 4;str_id=put(id,10.);return(substr(str_id,1,4));endsub;run;options cmplib=work.fun1.fun1;proc sql;select order_id, myfun(order_id) as first_4_lett fromecsql1.qtr1_2007 ;quit;8Сведение данных•Подсчет агрегатов (можно задавать имена, описания и форматы вывода)proc sql;select order_type, avg(quantity) format=3.1 label=“Average!",sum(total_retail_price) format=dollar10.

label=“Sum"from ecsql1.order_factgroup by 1; *possible in group by, order by;quit;•Having: фильтрация по агрегатам:proc sql;select order_type, count(distinct customer_id), sum(total_retail_price)from ecsql1.order_factgroup by order_typehaving sum(total_retail_price) > 30000;quit;•Calculated: Операции с агрегатамиproc sql;select order_type, count(distinct customer_id) as people,sum(total_retail_price) as summ,calculated summ/calculated people as ratiofrom ecsql1.order_factgroup by order_type;quit;9Сведение данных.

Remerge.• Эта особенность SAS SQL помогает сводить исходные данные и агрегатыбез вложенных запросов.• Задача: найти людей, которые имеют максимальную зарплату (salary) всвоей группе (gender).• Обычный SQL: с помощью вложенных запросовproc sql;select gender,salary from ecsql1.sales as t1,(select gender, max(salary) as maxsalaryfrom ecsql1.salesgroup by gender) as t2where t1.gender=t2.gender and t1.salary = t2.maxsalary;quit;• SAS SQL:proc sql;proc sql;select gender, salaryselect gender, salary,from ecsql1.salesmax(salary) as msgroup by genderfrom ecsql1.saleshaving salary = max(salary);group by genderquit;having ms=salary;quit;NOTE: The query requires remerging summary statistics back with the original10data.Использование макропеременных INTO•С точки зрения макропроцессора, Proc SQL – обычная процедура, поэтомуподстановка макропеременных происходит без проблем:%let stat=min;proc sql;select * from ecsql1.salesgroup by genderhaving salary=&stat.(salary);quit;•В proc sql вы можете создавать макропеременные:proc sql;select avg(salary) into :macro_salfrom ecsql1.sales;quit;%put macro_sal=¯o_sal;•И несколько («по горизонтали»!!!!) макропеременных (много трюков, см.справку по INTO Clause):11Использование макроопределенийoptions mprint mlogic symbolgen;%MACRO QTR12;proc sql;%do i=1 %to 2;select * from ecsql1.qtr&i._2007;%end;quit;%MEND;%QTR12MLOGIC(QTR12): Beginning execution.MPRINT(QTR12):proc sql;MLOGIC(QTR12): %DO loop beginning; index variable I; start value is 1; stop value is 2; byvalue is 1.SYMBOLGEN: Macro variable I resolves to 1MPRINT(QTR12):select * from ecsql1.qtr1_2007;MLOGIC(QTR12): %DO loop index variable I is now 2; loop will iterate again.SYMBOLGEN: Macro variable I resolves to 2MPRINT(QTR12):select * from ecsql1.qtr2_2007;MLOGIC(QTR12): %DO loop index variable I is now 3; loop will not iterate again.MPRINT(QTR12):quit;NOTE: PROCEDURE SQL used (Total process time):real time0.05 seconds.

. .MLOGIC(QTR12): Ending execution.12Взаимодействие со средой SAS из SQL•Можно через стандартные SAS функции, макросы, процедуры, наборыданных. A можно через специальную библиотеку DICTIONARY, напримерполучить список всех библиотек в сессии:•Доступные служебные «таблицы»:– LIBNAMES, MEMEBERS, TABLES, VIEWS, COLUMNS, INDEXES, FORMATS, ENGINES имногое другое•Как получить список всего, в том числе колонок?13Случайная выборка (sampling)• Простой вариант (например, без возвращения, без сохраненияраспределения переменных) можно сделать на SQL:• Более сложные (например, с возвращением и сохранениемраспределения переменной Type) потребует уже шага данных:14.

Характеристики

Тип файла PDF

PDF-формат наиболее широко используется для просмотра любого типа файлов на любом устройстве. В него можно сохранить документ, таблицы, презентацию, текст, чертежи, вычисления, графики и всё остальное, что можно показать на экране любого устройства. Именно его лучше всего использовать для печати.

Например, если Вам нужно распечатать чертёж из автокада, Вы сохраните чертёж на флешку, но будет ли автокад в пункте печати? А если будет, то нужная версия с нужными библиотеками? Именно для этого и нужен формат PDF - в нём точно будет показано верно вне зависимости от того, в какой программе создали PDF-файл и есть ли нужная программа для его просмотра.

Список файлов лекций