二、考试题目（后面我贴出我平时准备时练习的代码，接近原题，基本包括考试的所有题型）
我遇到的Project：
SQL
1. 从一个表中读name，要求name必须在另一个表的name中
select name from a
where name in (select name from b);
2. 从一个表中选 unique Make and Type， 从另一个表中匹配Rebate，有对应的Make 和 Type 则显示Rebate，否则为空。我自己用left join 做的。
. .и
select distinct c.Make, c.Type, r.Rebate from table1 c left join table2 r on
c.Make=r.Make and c.Type=r.Type
3. 从一个表中选择某一列并取平均值，将这个平均值给一个宏变量。
..
4. 从一个表中选某列的平均值，附加一些一些条件，并做排序。. From 1point 3acres bbs
四、我参考上述机经后准备的练习代码，应对考试题型基本没啥问题，数据在附件中，自己建一个也可以。
另外，强烈建议考前做以下那个practice，很有帮助
/*********************************************************************************
Prepare data
X1 and X2 are used as inputs.
/*********************************************************************************/
libname cert '/folders/myfolders/Certification_SAS for beginners_SAS codes/Cert_base/input/';
libname new xlsx '/folders/myfolders/Certification_SAS for beginners_SAS codes/Cert_base/input/input01.xlsx';
libname out '/folders/myfolders/Certification_SAS for beginners_SAS codes/Cert_base/output/';.
data x1;
set new.sheetc;
run;
data x2;
set new.sheeta;
run;
proc sql;
select * from dictionary.indexes
where libname=’SASUSER’ and memname=’SALE2000’;
quit;
/*********************************************************************************/
/*Function*/.google и
/*********************************************************************************/
/*---------------------------------------------
Function Exmple 1
proc fcmp IN to CM, 建好function后options cmplib=work.functions，然后建个新data里面
有个新列newheight=IN to CM（height）cm=2.45IN
----------------------------------------------*/. 1point3acres.com
proc fcmp outlib=work.functions.dev;. 1point 3 acres
function in2cm(in);
return(in*2.54);
endsub;
options cmplib=work.functions;
-baidu 1point3acres
Data ACT03;
Set x1;
NewHeight=in2cm(height);
Run;
/*---------------------------------------------.google и
Function Exmple 2
Weight change: lbs to kg
----------------------------------------------*/
proc fcmp outlib=work.a.c;
Function l2k(l);
k=0.9*l;
return(k);
endsub;.google и
run;
. .и
options cmplib=work.a;
data x2;
set x1;
new_weight=l2k(weight);.
run;
/*---------------------------------------------
Function Exmple 3
String as input. 数据集需要添加name= first name + second name，没时间就没加
----------------------------------------------*/
options cmplib=(work.functions);. ----
proc fcmp outlib=work.functions.dev;
function ReverseName(name $) $ ;
length newname $40;
newname=catx(' ',scan(name,2,','),scan(name,1,','));
return (newname);.google и
endsub;
data xx;
set yy;.1point3acres
zz=ReverseName(Name);
run;
/*********************************************************************************
Array.-- https://support.sas.com/resource ... s/sugi30/242-30.pdf
*********************************************************************************/
/*---------------------------------------------
Arrary Exmple 1 . check 1point3acres for more. Change all missing value to 0
----------------------------------------------*/
data new;
set x1;
array var(*) _numeric_;
do i=1 to dim(var);
if var(i)=. then var(i)=0;
end;
drop i;
run; ..
/*---------------------------------------------.
Arrary Exmple 2
把q1-q3的A，B，C，D，E换成1，2，3，4，5存在num1-num3中
----------------------------------------------*/
data new2;.
set x1;
array q(3) q1-q3;
array num(3) num1-num3;
do i=1 to 3;
if q(i)='A' then num(i)=1;
else if q(i)='B' then num(i)=2;
Else If q{i}='C' then num(i)=3;
Else If q{i}='D' then num(i)=4;. Waral dи,
Else If q(i)='E' then num(i)=5;
end;
run;
..
. From 1point 3acres bbs
/*---------------------------------------------.google и
Arrary Exmple 3
写一个array LS，有LENGTH1-LENGTH3, 另外一个array NEW，NEWLENGTH1-NEWLENGTH3.
有一个data set，LS单位是in，NEW是换算后的cm，NEW(i)=2.54*LS(i)
----------------------------------------------*/. From 1point 3acres bbs
data ls;
set x1;
array ls(*) LENGTH1-LENGTH3;
array new(*) NEWLENGTH1-NEWLENGTH3;
do i =1 to 3;
NEW(i)=2.54*LS(i);
end;
drop i;
run;
. 1point 3acres
/*********************************************************************************/
/*SQL*/
/*********************************************************************************/
/*---------------------------------------------
SQL Exmple 1 .google и
----------------------------------------------*/
Proc sql;.1point3acres
Create table sql01 as
Select *. Χ
From x1
Where name in (select name from x2);
Quit;
proc sql;
create table new_table as
select name, height, case
when height >=180 then 'high'
when height >=170 and height <180 then 'middle'
when height <170 then 'low'
end as height_marker from x1;. check 1point3acres for more.
quit;. 1point 3 acres
. ----
/*---------------------------------------------
SQL Exmple 3 . From 1point 3acres bbs
use SQL to create a new table that contains all unique values of Make and Type
from cert.cars, and Rebate from cert.rebate. For Rebate, show Make and Type if matching;
if no matching Make and Type, show missing. Only three variable: Make, Type, Rebate
----------------------------------------------*/
proc sql;. Waral dи,
select distinct x2.name, height, gender, benefit from x1 right join x2 on. check 1point3acres for more.
x1.name=x2.name;
quit; ..
. ----
-baidu 1point3acres
/*---------------------------------------------
SQL Exmple 4.--
给选出来的变量加label (select x label="foo", y)
----------------------------------------------*/.
proc sql;
select name label='Student Name', n1 label='Index1' from x1;
quit;
/*---------------------------------------------
SQL Exmple 5
having avg大于150 用department进行分组，按着一个variable进行sort
----------------------------------------------*/
proc sql;
create table xx as
select department, avg(height) as hei from x1
group by department. ----
having hei>150. 1point3acres
order by department;
quit;
.--
.google и
/*---------------------------------------------
SQL Exmple 6
Use dictionary.columns to get the list of variable name of a table.1point3acres
----------------------------------------------*/
proc sql;
select name into: collist separated by ','
from dictionary.columns . 1point3acreswhere libname='WORK' and memname='X1' and name like '%ght';
quit;.1point3acres
Data info19 errors19;
Length country name $30;
If _n_=1 then do;
Declare hash C(dataset:'cert.country19');
C.definekey('name');
C.definedata('population');
C.definedone();
Call missing (country,name);
end;. ----
set cert.country12;
rc=c.find(); /*rc=return code*/
if rc=0 then output info19;
else output errors19;
run;. .и
/*---------------------------------------------
Hash Exmple 2 .1point3acres
----------------------------------------------*/.1point3acres
data hash_sample;
input fruit$ amount;
cards;. 1point3acres
apple 100. check 1point3acres for more.
orange 200
tomato 300
cherry 400
cherry 500
;
run;
-baidu 1point3acres
data _null_;
length fruit $10. amount 8.;
if _n_ = 1 then do ;
/*Declare Hash Object*/
declare hash myhash (dataset:'hash_sample',duplicate:'r');/*Replace the duplicate, only keep the last one*/
rc = myhash.definekey('fruit');
rc = myhash.definedata('fruit','amount');
rc = myhash.definedone();
call missing(fruit,amount); /*When no matching values, return none*/ ..
end; .1point3acres
/*Check*/. 1point 3 acres
rc=myhash.find(key:'cherry'); /*find if cherry exist*/
if rc=0 then do; /*rc=0: find cherry*/
put fruit= amount= ;
end;.
/*Check and Replace*/
rc=myhash.check(key:'tomato');
if rc=0 then do;
put 'tomato is exist';
rc = myhash.replace(key:'tomato',data:'tomato',data:350);
end;
/*Find and put*/
rc=myhash.find(key:'tomato');
if rc=0 then do;. Χ
put 'new tomato' fruit= amount= ;. Χ
end;
/*Add a record*/
myhash.add(key:'peach',data:'peach',data:400);/*add new vaule*/
rc=myhash.find(key:'peach');.--
if rc=0 then do;
put 'new peach' fruit= amount= ;
end;.1point3acres
-baidu 1point3acres
/*If not exist, then add; otherwise keep it*/
myhash.ref(key:'balabala',data:'balabala',data:600);
rc = myhash.find(key:'balabala');
if rc=0 then do;
put 'balbala' fruit= amount= ;. Χ
end;
. From 1point 3acres bbs
/*Total number of records*/
totalitems = myhash.num_items;/*totalitems*/. 1point 3acres
put totalitems;
myhash.output(dataset:'out');/*Put data into 'out' dataset*/
run;
data success fail;
length name $10;
call missing(name);
if _n_=1 then do;
declare hash c(dataset:'xxx');
c.definekey(yy);
c.definedata(zz,name);. 1point3acres.com
c.definedone();
end;
set dajl;. 1point 3acres
if c.find() then output success;
else output fail;
run;
. 1point3acres
/*---------------------------------------------
Hash Exmple 3 .google и
----------------------------------------------*/
. 1point3acres
data work.success work.fail; /* 建立题目要你建立的dataset */. ----
drop rc; /*后面要用rc做查找，所以这里drop一下，跟你做do loop的时候drop i是一个意思*/
length YYName $30; /* YYName是你的definedata，很多时候原来没有，你得建立变量 */
if _N_=1 then do; /* 固定格式不谈*/
call missing (YYName); /*你的YYName不是刚刚建立的吗，空的不行，所以你得用这个call missing初始化他*/
declare hash C(dataset:'x1'); /* 声明你的hash表，hash名字和dataset一定会给你，记得单引号 */
c.definekey('ID'); /* 定义你的hash key，就是那个ID，key在题目里,记得单引号 */
c.definedata('YYName'); /* 定义你的hash data ，就是那个YYName在题目里，一般就是我们刚刚Length的那货 */ -baidu 1point3acres
c.definedone(); /* hash定义完的声明，固定格式*/
end; /*这里是end上面那个do的，还记得有个do吗朋友*/-baidu 1point3acres
..
set cert.planes; /* 这里是你要去用你刚刚建立的hash表查找的另一个表，跟定义hash的表不同 */
rc=c.find(); /* 你可以理解为哈希表的查找结果，如果RC=0，就说明查找成功，这个cert.planes里面有哈希表这样一个对应关系，如果rc不等于0 ，说明没有*/
if rc=0 then output work.success; /* 如果rc=0，查找成功，那么就输出成功表 */
if rc ne 0 then output work.fail; /* 如果rc不等于0，查找失败，那么就输出失败表 */
run;
-baidu 1point3acres
狗仔卡
收藏
淘帖
好苗
杂草
提升卡
置顶卡
关贴卡
开贴卡
变色卡
千斤顶
楼主
