proc sql;
select
indxname,
name , /* column name */
unique ,
idxusage, /* SIMPLE, COMPOSITE */
indxpos /* position of column in concatenated key */
from
dictionary.indexes
where
libname = 'TQ84_LIB' and
memname = 'TQ84_MEMBER'
order by
indxname,
indxpos;
quit;
proc sql;
describe view sashelp.vallopt;
quit;
/*
SQL view SASHELP.VALLOPT is defined as:
select *
from DICTIONARY.OPTIONS
union
select *
from DICTIONARY.GOPTIONS;
*/
data _null_;
/* Comments are either between
slash-star and star-slash */
* or between * and semicolon;
%macro temporary_commented;
A macro definition with any name (such
as temporary_commented) can be used to
comment a block of code
/* foo bar baz */
%mend;
put 'Hello world.';
run;
data tq84_one;
infile datalines;
length col_1 $10;
length col_2 $10;
length col_3 $10;
input
col_1
col_2
col_3
;
datalines;
x y z
one two three
foo bar baz
strawberry blackberry blueberry
;
data tq84_two;
set tq84_one;
drop col_2; /* Exclude variable col_2 from output */
col_4 = _N_; /* Add new variable to output */
run;
proc print data=tq84_two;
run;
data _null_;
if libref("tq84_l") ne 0 then
put "tq84_l is not assigned";
else
put "tq84_l is assigned";
run;
libname tq84_l "s:\Atrans\SASCC\tq84";
data _null_;
if libref("tq84_l") ne 0 then
put "tq84_l is not assigned";
else
put "tq84_l is assigned";
run;
libname tq84_l clear;
data tq84_one;
infile datalines;
length col_2 $10;
input
col_1
col_2
;
datalines;
1 one
2 two
3 three
4 four
5 five
6 six
7 seven
8 eight
9 nine
;
data tq84_two;
set tq84_one;
/* Compare with subsetting if */
where col_2 gt 's';
run;
proc print data=tq84_two;
run;
data tq84_one;
infile datalines;
length col_1 $10;
length col_2 $10;
length col_3 $10;
length col_4 $10;
length col_5 $10;
input
col_1
col_2
col_3
col_4
col_5
;
datalines;
a b c d e
one two three four five
foo bar baz qux quux
morning noon afternoon evening night
;
data tq84_two;
set tq84_one;
keep col_2 col_4; /* Drop all variables except col_2 and col_4 */
run;
proc print data=tq84_two;
run;
/*
With the mprint system option turned on, SAS will print
the generated code into the log.
This is useful for debugging purposes.
*/
options mprint;
%macro tq84_m(nof_obs, nof_attrs);
data tq84_data;
drop obs;
do obs=1 to &nof_obs;
%do varno = 1 %to &nof_attrs;
var_&varno = rand('norm');
%end;
output;
end;
run;
%mend tq84_m;
%tq84_m(5, 3);
/*
MPRINT(TQ84_M): data tq84_data;
MPRINT(TQ84_M): drop obs;
MPRINT(TQ84_M): do obs=1 to 5;
MPRINT(TQ84_M): var_1 = rand('norm');
MPRINT(TQ84_M): var_2 = rand('norm');
MPRINT(TQ84_M): var_3 = rand('norm');
MPRINT(TQ84_M): output;
MPRINT(TQ84_M): end;
MPRINT(TQ84_M): run;
*/
/* Use msglevel=I to print index usage into log */
options msglevel=I;
libname tq84_sql 'p:\ath\to\some\directory';
proc datasets
library=tq84_sql
kill;/* Delete all files in the library at once. */
run;
data tq84_sql.english_numbers;
length num 4. txt $ 10;
input num txt;
datalines;
1 one
2 two
3 three
4 four
5 five
6 six
7 seven
8 eight
9 nine
10 ten
run;
data tq84_sql.roman_numbers;
length num 4. txt $ 10;
input num txt;
/* Note the missing numbers 4, 7 and 8 */
datalines;
1 I
2 II
3 III
5 V
6 VI
9 IX
10 X
run;
%macro left_join_nums;
proc sql;
select
en.num,
en.txt,
rm.txt
from
tq84_sql.english_numbers en left join
tq84_sql.roman_numbers rm on en.num = rm.num
where
en.num = 7;
quit;
%mend left_join_nums;
%macro create_index_nums(tab);
proc datasets library=tq84_sql;
modify &tab;
index create num / unique;
run;
%mend create_index_nums;
%left_join_nums;
%create_index_nums(english_numbers);
%left_join_nums;
/* Because of 'options msglevel=I', log says:
INFO: Index num selected for WHERE clause optimization.
*/
%create_index_nums(roman_numbers);
%left_join_nums;
Determine whether or not statements are written to the log:
/* Don't write statements to the log file: */
options nosource;
data _null_;
put "foo";
run;
/* Write statements to the log file: */
options source;
data _null_;
put "bar";
run;
data tq84_data;
length num 8
txt $ 10;
input num txt;
datalines;
1 one
2 two
3 three
4 four
5 five
6 six
7 seven
8 eight
9 nine
10 ten
run;
proc sql outobs=5;
select
num,
txt
from
tq84_data
order by
ranuni(280870);
quit;
data tq84_data;
length num 8
en $ 10
gr $ 10
fr $ 10;
input num en gr fr;
datalines;
1 one eins un
2 two zwei deux
3 three drei trois
4 four vier quattre
5 five fünf cinque
run;
proc sql;
/* Select all columns except fr: */
select *
from tq84_data(drop=fr);
quit;
data tq84_data;
length id 8
foo $ 10
bar $ 10
baz $ 10;
input id foo bar baz;
datalines;
1 one xxx abc
2 two abc def
3 three abc .
4 four xxx ghi
5 five def .
6 six abc jkl
7 seven xxx mno
8 eight def pqr
9 nine abc .
10 ten xxx stu
run;
proc sql;
select
ifc (uq_foo eq cnt, 'foo is unique', 'foo is not unique'),
ifc (uq_bar eq cnt, 'bar is unique', 'bar is not unique'),
ifc (uq_baz eq cnt, 'baz is unique', 'baz is not unique')
from (
select
count(*) as cnt,
count(unique(foo)) as uq_foo,
count(unique(bar)) as uq_bar,
count(unique(baz)) as uq_baz
from
tq84_data
);
quit;
%let ora_user = rene;
%let ora_password = secret_garden;
%let ora_server = ora.test.renenyffenegger.ch;
data tq84_stage;
length spelled $20;
input
val
spelled $
;
datalines;
1 one
2 two
3 three
4 four
5 five
42 forty-two
;
proc sql;
--
-- Apparently, the following "connect to oracle"
-- statement is referred to as "pass through".
--
connect to oracle as tq84_ora (
user = &ora_user
password = &ora_password
path = &ora_server
);
create table
tq84_ora.a_table_name_3
as
select * from tq84_stage;
quit;
data tq84_data;
length num 8
en $ 10
gr $ 10
fr $ 10;
input num en gr fr;
datalines;
1 one eins un
2 two zwei deux
3 three drei trois
4 four vier quattre
5 five fünf cinque
run;
proc sql;
create table tq84_long_col_names(
rename=(en=english
fr=french
gr=german)
)
as
select *
from tq84_data;
quit;
data tq84_data;
length num 8;
length txt $ 10;
input num txt;
datalines;
1 one
2 two
3 three
4 four
5 five
6 six
7 seven
8 eight
9 nine
10 ten
run;
proc sql;
select
num,
txt
from
tq84_data
where
/* Note: monotonic() evaluated before
order by */
monotonic() between 4 and 7
order by
txt;
quit;
proc sql;
select
a.name as name_a,
b.name as name_b,
soundex(a.name) as name_soundex
from
sashelp.class a join
sashelp.class b on soundex(a.name) = soundex(b.name)
where
a.name gt b.name;
quit;
data _null_;
array ary_a{ *} a1-a10;
array ary_b{ 10} b1-b10;
array ary_c{5:14} c1-c10;
/* Determine the number of elements in an array */
dim_a = dim(ary_a);
dim_b = dim(ary_b);
dim_c = dim(ary_c);
put dim_a=; /* dim_a=10 */
put dim_b=; /* dim_b=10 */
put dim_c=; /* dim_c=10 */
run;
data xy;
/* Keep x and y, don't output i and r */
keep x y;
do i = 1 to 100;
r = rand('norm');
x = r ** 2;
y = x ** 2;
output;
end;
run;
proc print
data = xy;
run;
data tq84_data;
length num 8
en $ 10
fr $ 10
gr $ 10;
input num en fr gr;
datalines;
1 one un eins
2 two deux zwei
3 three trois drei
4 four . vier
5 five cinque fünf
6 six . .
7 seven . sieben
8 eight huit .
9 nine neuf neun
10 ten dix zehn
run;
%let lib=WORK;
%let tab=TQ84_DATA;
proc sql noprint;
select
catx(' ', "count(*) - count(distinct ", name,") as ", name)
into
:cols separated by ','
from
sashelp.vcolumn
where
libname="&lib" and
memname="&tab";
quit;
%put &cols;
proc sql noprint;
create table
cnt_dist_&tab
as
select
&cols
from
&lib..&tab;
quit;
proc sql;
select * from cnt_dist_&tab;
quit;
proc transpose
data=cnt_dist_&tab
out=unique_&tab;
run;
proc sql;
select
catt(_name_, ' is ', ifc(col1 eq 0, '', ' not'), ' unique')
from
unique_&tab;
quit;
/* %datatyp returns either NUMERIC or CHAR */
data _null_;
%let fourty_two = 42;
%let foo ="bar";
put "fourty_two is %datatyp(&fourty_two)";
put "foo is %datatyp(&foo)";
run;
%macro foo;
this is from macro foo
%mend foo;
%macro bar;
this is from macro bar
%mend bar;
%macro put_named_macro(macro_name);
%put macro ¯o_name expands to >%¯o_name<;
%mend put_named_macro;
%put_named_macro(foo);