/*-------------------------------------------------------------------\
| Creating a Table and Inserting Data into It; |
| 1) create a new table; |
| 2) insert data into the table; |
| INSERT INTO table-name|sas/access-view|proc-sql-view |
| <(column<,column>...)><,user-name>...; |
| SET column=sql-expression <,column=sql-expression>... |
| ...>; |
| |
| INSERT INTO table-name|sas/access-view|proc-sql-view |
| <(column<,column>...)> |
| VALUES (value <, value>...) |
| ...)>...; |
| |
| INSERT INTO table-name|sas/access-view|proc-sql-view |
| <(column<,column>...)> query-expression; |
\-------------------------------------------------------------------*/
proc sql;
create table proclib.paylist
(IdNum char(4),
Gender char(1),
Jobcode char(3),
Salary num,
Birth num informat=date7.
format=date7.,
Hired num informat=date7.
format=date7.);
insert into proclib.paylist
values('1639','F','TA1',42260,'26JUN70'd,'28JAN91'd)
values('1065','M','ME3',38090,'26JAN54'd,'07JAN92'd)
values('1400','M','ME1',29769.'05NOV67'd,'16OCT90'd)
values('1561','M',null,36514,'30NOV63'd,'07OCT87'd)
values('1221','F','FA3',.,'22SEP63'd,'04OCT94'd);
title 'PROCLIB.PAYLIST Table';
select *
from proclib.paylist;
quit;
/*
Purpose: This example creates a new table, PROCLIB.NEWPAY,
by concatenating two other tables: PROCLIB.PAYLIST and
PROCLIB.PAYLIST2; then delete the rows of which some condition
is met;
1) DELETE statement: delete rows;
2) IS condition;
3) RESET statement option DOUBLE: add new options, double
space the report, check sql options for details;
4) UNION set operator;
*/
proc sql;
create table proclib.newpay as
select * from proclib.paylist
union
select * from proclib.paylist2;
delete
from proclib.newpay
where jobcode is missing or salary is missing;
reset double;
title 'Personnel Data';
select *
from proclib.newpay;
quit;
/*-------------------------------------------------------------------\
| Restriction: the engine must support UPDATE processing. |
| 1) Drop: |
| DROP TABLE table-name <,table-name>...; |
| DROP VIEW view-name <,view-name>...; |
| DROP INDEX index-name <,index-name>... |
| FROM table-name; |
| 2) Update: |
| UPDATE table-name|sas/access-view|proc-sql-view |
| SET column=sql-expression <,column=sql-expression>... |
| ...> |
| ; |
| 3) Case: |
| CASE |
| WHEN when-condition THEN result-expression |
| ... |
| |
| END |
| 4) Add Columns or delete Columns: |
| ALTER TABLE table-name |
| <, constraint-clause>...>; |
| ...> |
| ...> |
| ...>; |
| where each constraint-clause is one of the following: |
| ADD constraint |
\-------------------------------------------------------------------*/
/* Example 1 */
proc sql;
title 'Employees Table';
select * from Employees;
update employees
set salary=salary*
case when jobcode like '__1' then 1.04
else 1.025
end;
alter table employees
modify salary num format=dollar8. /* Change a Column */
drop phone /* Delete a Column */
add keyterm char(10); /* ADd a column */
update totpt
set keyterm='Total';
title 'Updated Employees Table';
select * from employees;
quit;
/* Example 2 */
proc sql;
create table totpt as
select count(*) as numevt, count(distinct pt) as numpt
from ae
where keyterm is not missing;
alter table totpt
add keyterm char(10);
update totpt
set keyterm='TOTAL';
select distinct a.keyterm,
count(*) as numevt label="Number of Events",
count(distinct pt) as numpt label="Number of Patients",
count(distinct pt)*100/t.numpt format=pctfmt. label="Percentage of Patients"
from ae as a, totpt as t
where a.keyterm is not missing
group by a.keyterm
outer union corresponding
select * from totpt;
quit;
/*-------------------------------------------------------------------\
| 1) SQL: Retrieve data from database efficiently; |
| Including Oracle Clinical and Device Database: |
| Note: For Oracle Clinical Database, you will have to specify the|
| schema for 3 or more than 3 levels tables; |
| Create Keys and index for a dataset; |
| This is the most efficient way of all; |
| 2) Get aroung invalid date problem: |
| if only year is provided, use the Middle of the year ; |
| if only year and month is provided, use the Middle of the month;|
| if the date is missing, do nothing; |
| else entire date is provided; |
| 3) "dquote" option is used to support special column or tables that|
| are not nornally supported; |
\-------------------------------------------------------------------*/
/* Oracle Clinical */
libname OCdbms odbc dsn="pocl" user="OPS$XIAA1" password="XIAA1" access=readonly
connection=GLOBALREAD schema="ACEDAF$CURRENT";
proc sql dquote=ansi;
select case
when missing(condt) then .
when length(condt) < 4 and not missing(condt) then .
when length(condt) >= 4 and length(condt) < 6 then (INPUT((SUBSTR(condt,1,4)||'1231'), YYMMDD8.)+INPUT((SUBSTR(condt,1,4)||'0101'), YYMMDD8.)+1)/2
when length(condt) >= 6 and length(condt) < 8 then (INPUT((SUBSTR(condt,1,6)||'01'), YYMMDD8.) + 15)
else INPUT(SUBSTR(condt,1,8), YYMMDD8.)
end as condt format date9.,
condt as oricondt label="Original CONDT"
from patdvn;
quit;
/* Device Database */
libname DVdbms odbc dsn='device' user=dr password=dr access=readonly connection=GLOBALREAD;
proc sql dquote=ansi;
Create table ACEDdev.EPISODE as Select
COMP_SERNUM as serialno label="serialno",
EPIS_TYPE_CODE as epitype label="Episode Type",
EPIS_ID as epiid label="Episode ID",
EPIS_DETECT_DATE as epidt label="Episode Date"
from DVdbms.EPISODE(dbkey=comp_sernum)
where COMP_SERNUM /*in &acedallserial*/
order by comp_sernum;
create view DVviews.epiview as
select *
from DVdbms2.episode as epi
using libname DVdbms2 odbc
dsn=device user=dr password=dr access=readonly;
quit;
/* Create keys for a Data set */
data mydata;
set keyvalues;
set mydblib.employees(dbkey=(empid jobcode))
key=dbkey;
run;
/* Join two tables from different ODBC data sources */
/*************************************************************************/
/* Join two tables via SAS/ACCESS to ODBC. In this example the two */
/* tables are not part of the same ODBC datasource, but the use of SQL */
/* aliases allows them to be joined in the same PROC SQL step */
/*************************************************************************/
proc sql;
connect to odbc as db_1 (dsn=db1 uid=joe pwd=schmo);
connect to odbc as db_2 (dsn=db2 uid=joe pwd=schmo);
create table db1.table3 as
select * from connection to db_1
(select * from table1) as table1,
connection to db_2
(select * from table2) as table2
where table1.id = table2.id;
disconnect from db_1;
disconnect from db_2;
quit;
/*-------------------------------------------------------------------\
| Keyword: Database time |
| 1) Retrieve the Database systime from the remote server; |
| 2) Suppress the NOTES in the log window for the all following SAS |
| statements; |
\-------------------------------------------------------------------*/
OPTIONS NONOTES;
proc sql;
connect to odbc (dsn="pocl" user="OPS$XIAA1" pwd="XIAA1");
create table systime as select * from connection to odbc
( select
SYSDATE database_datetime,
to_char(SYSDATE, 'DD/MM/YYYY') database_date,
to_char(SYSDATE, 'HH24:MI:SS') database_time
from
DUAL
);
disconnect from odbc;
quit;
Data _null_;
format database_datetime datetime.;
SET SYSTIME;
CALL SYMPUT("dbtime", trim(left(put(database_datetime,datetime21.))));
run;
%put Database time is &dbtime..;
/*-------------------------------------------------------------------\
| Usage of |
| 1) Proc sql; |
| 2) case, exists, in, having, group by |
| 3) subquery, outer join, union |
| 4) concatenate the variables into a long macro variables |
| 5) Keyword: |
| OUTER UNION - concatenates the query results; |
| a) stack the two data; |
| b) put the variables side by side even if they |
| have the same variable names; |
| UNION - produces all unique rows from both queries; |
| EXCEPT - rows that are part of the first query only; |
| INTERSECT - rows that are common to both query results; |
| Options: |
| CORRESPONDING/CORR: option for OUTER UNION; |
| ALL: option for UNION; |
| CORR: a) stack 2 data sets column by column correspondingly, |
| if they have the same variable names; |
| b) create missing values for variable names that exist |
| in only one of the 2 data set; |
| ALL: keep duplicate rows; |
\-------------------------------------------------------------------*/
proc sql; /* Case */
select *, case
when degrees > 80 then 'Hot'
when degrees < 40 then 'Cold'
else 'Mild'
end
from temperatures;
select *, case Degrees
when > 80 then 'Hot'
when < 40 then 'Cold'
else 'Mild'
end
from temperatures;
quit;
proc sql outobs=10; /* select number of observations */
title 'All Flights for Each Supervisor';
select *
from (select lname, s.idnum, city, jobcat, flight, date
from proclib.schedule2 s, proclib.staff2 t, proclib.superv2 v
where s.idnum=t.idnum and t.idnum=v.supid)
as three (Surname, Emp_ID, Hometown, Jobtype, FlightNumber, FlightDate);
quit;
proc sql;
select *
from proclib.payroll p
where exists (select * /* Exists */
from staff s
where p.idnum=s.idnum
and state='CT');
/* Same as Previous */
select *
from proclib.payroll
where idnum in (select idnum /* In */
from staff
where state='CT');
quit;
/* Subquery */
proc sql;
select * from proclib.payroll
where salary> all(select salary
from proclib.payroll
where jobcode='ME3');
/* Same as previous */
select * from proclib.payroll
where salary> (select max(salary)
from proclib.payroll
where jobcode='ME3');
quit;
/* Outer Join */
proc sql;
title 'Full Outer Join';
select *
from lefttab as l full join righttab as r
on l.continent=r.continent;
quit;
/* Union */
proc sql;
create table proclib.newpay as
select * from proclib.paylist
union
select * from proclib.paylist2;
delete
from proclib.newpay
where jobcode is missing or salary is missing;
reset double;
title 'Personnel Data';
select *
from proclib.newpay;
quit;
/* select records with multiple repeat observations */
proc sql ;
select *
from test
group by user_id
having count(distinct session) gt 1
order by user_id, session, sequence, pagename;
quit;