sas学习笔记.docx
《sas学习笔记.docx》由会员分享,可在线阅读,更多相关《sas学习笔记.docx(19页珍藏版)》请在冰豆网上搜索。
sas学习笔记
SAS学习笔记
1新建逻辑库
工具栏——newlibrary——输入name、path、勾选启动默认enableatstartup
查看一下:
菜单——view——explorer
2新建数据集
方法1
Explorer——右键单击——new——table——输入数据,点击保存——选择逻辑库,填写名称——save
方法2
Solutions——analysis——analyst——输入数据,点击保存——选择逻辑库,填写名称——save
3导入外部数据
FILE——Importdata——勾选标准数据源standarddatasoruce——路径不能有中文字符
4排序
File——open——excel.sas7bdat文件
Data——sort——选择排序的字段——sortby——ascend升序、dascend降序——reset重新设置
5筛选
Data——filter——subsetdata——选择GE大于等于——《constantentervalue》——3
就是筛选subsetdata大于等于3,none是取消
6转置
Data——transpose——选择列——transpose转置——groupby分租
7频率分析
Statistics统计——descriptive描述——频率分析frequency——选择分析的字段
Input显示顺序plots条形图【hor水平vert垂直】
Tables输出内容
Sas语法基础
2.1创建客户信息数据集
libnamejx'e:
\sas\jx';
datajx.custer;
inputidname$sex$money
cards;
1001zhangfeimen98
1002liubeimen76
1003daocanwomen54
;
run;
procprintdata=jx.custer;
run;
2.2建立与文件连接的逻辑库
libnamejxsj'e:
\sas\jx'compress=yes;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sas如何连接sql2000数据库
odbc连接方式
建立odbc数据源:
控制面板-管理工具-数据源(odbc)
代码实现:
Libnamejmssqlodbcuser='sa'password=''dsn=mssql;
Oledb连接方式
代码实现
libnamesqlbaseoledbprovider=sqloledb.1properties=("DataSource"=MY3KQFHEPCPN95N"UserID"=sa"Password"=''"InitialCatalog"=KIS_Sample);
输出数据
procprintdata=Jmssql.t_Balance;
run;
2.3sql过程连接关系数据库
定义odbc连接变量
/*%letjx_connect=user='sa'password=''dsn='mssql';*/
定义oledb连接变量
%letjx_connect=user='sa'password=''provider=sqloledb.1properties=("DataSource"=MY3KQFHEPCPN95N"UserID"=sa"Password"=''"InitialCatalog"=KIS_Sample);;
procsql;
validate;
connecttooledb(&jx_connect);
select*into:
v_sjfromconnectiontooledb
(selectFDatefromt_Voucher);
/*execute(createtablejxcasselect*fromt_Voucher)byoledb;*/
disconnectfromoledb;
quit;
在表中定义变量并赋值
datawo.bl;
x=2;
y='abc';
run;
定义变量sf长度为18字符
datawo.custer;
lengthsf$18.;
inputname$sf;
cards;
zhangfei1234567890
liubei9123456780
;
run;
y自动转为数值参加计算
dataqsum;
x=1;
y='2';
sum=x+y;
run;
连接运算,数字转化为字符
datasubstr;
y=10;
ch='hellow';
v_sub=ch||y;
run;
字符替换与提取字符
Substr(s,p,n)从字符串s中的第p个字符开始提取n个字符的子串。
datasubstr1;
y=12345678;
转为12位字符,前面填充4个0
ch=substr(y,5,6);
bc=Ktruncate(y,5,6);
ch2=substr(y,5,3);
bc2=Ktruncate(y,5,3);
run;
常量赋值
datacl;
cl1='iloveyou!
';
cl2="i'mzhangfei";
cl3='nishi"xigua"';
run;
日期常量
datadtime;
c1='3mar2012'd;
c2='8:
30't;
c3='6apr2012:
8:
18:
30pm'dt;
run;
procprint;
/*formatc1yymmdd10.c2time10.c3datetime22.;*/
formatc1yymmdd.c2time.c3datetime.;
run;
if语句
libnamewo'E:
\sas';
datawo.custer;
inputid$level$amount5.@;
labelid='bianhao'level='jibie'amount='edu';
cards;
1001a2000
1002b2003
1003b3000
1004c5000
1005d6000
;
datawo.aleiwo.bleiwo.clei;
setwo.custer;
iflevel='a'thenoutputwo.alei;
elseiflevel='b'thenoutputwo.blei;
elseoutputwo.clei;
run;
procprint;
title"[cleixinyongka]";
run;
读取前3条记录
libnamewo'E:
\sas';
datawo.custer;
inputid$level$amount5.@;
labelid='bianhao'level='jibie'amount='edu';
cards;
1001a2000
1002b2003
1003b3000
1004c5000
1005d6000
;
datawo.new1;
setwo.custer;
if_n_<3thenoutputwo.new1;
run;
procprintdata=wo.new1;
title"[new1xinyongka]";
run;
selectwhen判断语句
libnamewo'E:
\sas';
datawo.custer;
inputid$level$amount5.@;
labelid='bianhao'level='jibie'amount='edu';
cards;
1001a2000
1002b2003
1003b3000
1004c5000
1005d6000
;
datawo.aleiwo.bleiwo.clei;
setwo.custer;
select(level);
when('a')outputwo.alei;
when('b')outputwo.blei;
otherwiseoutputwo.clei;
end;
run;
selectwhen修改符合条件的记录
procprintdata=wo.blei;
title"[bleixinyongka]";
run;
libnamewo'E:
\sas';
datawo.custer;
inputid$level$amount5.@;
labelid='bianhao'level='jibie'amount='edu';
cards;
1001a2000
1002b2003
1003b3000
1004c5000
1005d6000
;
datawo.new2;
setwo.custer;
select;
when(level='a')level='m';
when(level='b')level='n';
otherwiselevel='s';
end;
run;
procprintdata=wo.new2label;
title"[new2xinyongka]";
run;
dowhile循环语句:
先判断在执行
libnamewo'E:
\sas';
datawo.custer;
inputid$level$amount5.@;
labelid='bianhao'level='jibie'amount='edu';
cards;
1001a2000
1002b2003
1003b3000
1004c5000
1005d6000
;
run;
datawo.new3;
x=0;
dowhile(x<11);
x=x+2;
end;
putx=;
run;
x=12
dountil循环语句:
先执行再判断
datawo.new3;
x=0;
dountil(x<11);
x=x+2;
end;
putx=;
run;
x=2
doto循环语言
datawo.new4;
x=0;
doi=1to9by2;
x=i+1;
end;
run;
操作符
datawo.new4;
y=2**3;乘方
x=5/2;除
run;
and&or|,||连接,!
!
连接
宏变量%let
格式修饰符冒号
对于数据长度大小不定的列,加:
冒号,防止读错列.
对于有空格的列,加&,保留空格
对于有引号,分隔号的列,加~
datawo.new4;
inputname$address&:
$20.zone$@;
cards;
zhangfeizhongguobeijingfengtaiqu100070
liubeibeijingxichengqu100000
guanyuzhongguoshandong272195
;
run;
指针控制读取文件
%letlujing='D:
\jx\tx.txt';
filenametx0"(&lujing)";
datanew5;
infiletx0;
input@1qh$4.@5address$17.@22youbian$4.
;
run;
procprint;
run;
@行控制符号
%letlujing='d:
\jx\credtype.txt';
filenamecred1"(&lujing)";
datawo.xinyongka;
infilecred1;
input@20card_type$1.
@;
ifcard_type='B'thendo;
input@1credt_bh$3.
@4card_num$16.
;
outputwo.xinyongka;
end;
run;
procprintdata=wo.xinyongkanoobs;
run;
@@换行
datacust_inf;
lengthaddress$20.;
inputname$address@@;
cards;
liubeibeijingfengtaizhangyuhuahebeitangxiandongxiaoqingjiangsunanjing
;
run;
procprintdata=cust_inf;
varnameaddress;
run;
put数据输出到外部文件
%letlujing='d:
\jx\filewrite.txt';
filenamewbsj"(&lujing)";
datawo.gs;
infilewbsj;
input@1credt_bh$3.
@4num$16.
@20type$1.
;
run;
%letlujing='e:
\sas\filewrite.txt';
filenamewbsj2"(&lujing)";
data_null_;
filewbsj2;
setwo.gs;
put@1credt_bh$3.
@4'|'
@5num$16.
@21'|'
@22type$1.
;
run;
读取外部文件,从第一条到第5条
%letlujing='e:
\sas\test1.txt';
%letfilename="&lujing";
datawo.test1;
infile&filenamemissoverlrecl=300firstobs=1obs=5;
inputf1$
f2$
f3$
;
run;
输出数据到外部文件
data_null_;
setwo.test1;
file'e:
\sas\test2.txt';
putf1$
s'|'
f2$
b'|'
f3$
;
run;
在mssql中建表
createtablejx_inf(idchar(8),hostvarchar(15),addressvarchar(30),feesnumeric(5,1),time_lennumeric(4),arrears_countnumeric(3),last_mtimeldatetimedefaultgetdate(),curr_mtimeldatetime,next_mtimeldatetime);
insertintojx_infvalues('12345676','刘小红','北京市丰台区丰台总部',300,20,7,CONVERT(varchar(100),'2014-04-1709:
01:
10',20),convert(varchar(100),'2014-04-1610:
01:
10',20),convert(varchar(100),'2014-04-1812:
01:
10',20))
将数据库中的表输出到文件
%leta='MY3KQFHEPCPN95N';
%letb='sa';
%letc='';
%letd='testdb';
libnamemyku1oledbprovider=sqloledb.1properties=("DataSource"=&a"UserID"=&b"Password"=&c"InitialCatalog"=&d);
%letout='e:
\sas\cust_inf.dat';
data_null_;
file&outlrecl=134;
setmyku1.jx_inf;
put@1id$8.
@9'|'
@10host$15.
@25'|'
@26address$30.
@56'|'
@57fees5.1
@62'|'
@63time_len4.0
@67'|'
@68arrears_count3.0
@71'|'
@72last_mtimeldatetime8.1
@80'|'
@81curr_mtimeldatetime8.1
@89'|'
@90next_mtimeldatetime8.1
@98'|'
;
注意:
日期无法显示
run;
读取bat文件,‘,’为分割符
%letlujing='d:
\jx\bl.dat';
filenamewbwj"(&lujing)";
databl;
infilewbwjdlm=',';
lengthfh$50.;
inputbh$fhjylcssr;
run;
读取日期格式
datacard1;
inputid$name$dtdate.;
cards;
1001gaoxiaohong10mar12
1002liuxiaoxia18mar12
;
run;
procprintlabel;
formatdtyymmdd10.;
run;
informat与format定义格式
datacard2;
informatdtmmddyy10.;
lengthdq$30.;
inputdq$dt;
cards;
beijing10-23-2010
shandong09-08-2011
;
run;
procprint;
formatdtyymmdd10.;
run;
数组
datatt;
arraytemp1
(2)$('a''b');
y=temp1
(2);
puty=;
run;
系统选项option
%macrodefine_op(v_bh);
optionobs=2compress=yes;
optionmlogicmprint;
%letdir='d:
\jx\bl.bat';
filenamewbwj"(&dir)";
datablsj;
infilewbwjdlm=',';
lengthfh$50.;
inputbh$fhjylcssr;
run;
procsql;
select*fromblsjwherebh=&v_bh;
quit;
%menddefine_op;
%define_op('01002');