重庆最近新闻,做搜狗网站优化排,建筑装修,徐州网页设计培训自我愚见#xff0c;望有错指出改之。/*SQL 学习*//*数据定义语言(DDL)#xff1a;create、drop、alter*//*数据操作语言(DML)#xff1a;insert、update、delete*//*数据查询语言(DQL)#xff1a;select*//*数据控制语言(DCL)#xff1a;grant、remove、commit、rollback…自我愚见望有错指出改之。/*SQL 学习*//*数据定义语言(DDL)create、drop、alter*//*数据操作语言(DML)insert、update、delete*//*数据查询语言(DQL)select*//*数据控制语言(DCL)grant、remove、commit、rollback*//*SELECT语句*/proc sqlselect ...from..where..group by..having..根据groupby得到子数据集order by..;/*EX1*/data a;set sashelp.class;where a70;run;proc sql;select sexFfrom sashelp.classfit;quit;proc sql;select sexFfrom sashelp.classfit;quit; /*s输出的结果是111000.... 表示如果是符合sex‘F’的就是1*//*利用SQL创建新表*/proc sql;create tablenew(表的名字) asselect * (代表选择全部)from ..;quit;/*利用列创建新变量*/proc sql;create tablenew asselect*,weight/height as ratio /*这个蛮有意思ratio是新的列名*/format5.2 label体重身高比fromsashelp.classfit;quit;/*利用SQL进行数据统计 类似Procsummary */proc sql;create tablenew1 asselect min(age) as youngest,max(age) as oldest,mean(height) as h format5.1from sashelp.classfit;quit;/*等同于下面的语句*/proc summary datasashelp.classfit;var age height;output outnew2(drop_type_ _freq_)/*删去两个列的统计量*/min(age)yougestmax(age)oldgestmean(height)h; /*记住分号别忘了*/run;/*利用SQL进行分组统计 加入了groupby*/proc sql;create tablenew3 asselect sex,min(age) as youngest,max(age) as oldest,mean(height) as h format5.1from sashelp.classfitgroup bysex;quit;/*等同于下面的语句*/proc summary datasashelp.classfit;class sex;var age height;output outnew4(drop_type_ _freq_)min(age)yougestmax(age)oldgestmean(height)h;run;/*根据条件语句创建数据集CASE WHEN ..THEN*/proc sql;create tablenew5 asselect name,age,sex,case whenage11 then zoowhen sexF then manelse [none]endas nenene /*是对新的 zoo man 这列命名*/from new;quit;/*利用where创建子数据集 简单*/proc sql;create tablenew6 asselect *from newwhere sexF;quit;/*利用having从句代替where从句*/proc sql;create tablenew7 asselect*from newgroup bysex,agehaving sexF;quit;/**必记having只能在groupby 之后 where必须在其之前**//*利用distinct剔除重复记录 **这个有陷阱见笔记*/proc sql;create tablenew8 asselect distinct*from new;quit; /*这个例子看不出效果 是以行为标准*//*****************************************************//*SQL提升*//*一.将原始变量和二次分析生成的变量进行合并*/proc sql;create tablenew9 asselect name,sex,count(*) asmany label统计fromnewgroup by ageorder by name ;quit;proc sql;create tablenew10 asselect name,sex,count(distinctsex) as many label统计fromnewgroup by ageorder by name ;quit; /** Really 看不懂这的结果为什么输出来那样怎么做到的统计哦哦 看明白了 是利用年龄的统计 后面distinct age 剔除重复的年龄再统计*//*二.SQL语言的嵌套*/proc sql;create tablenew11 asselect *from(selectage, /*标点*/count(*)asmanyfrom newgroup by age)having manymax(many);/*重点*/quit;/*下面是不嵌套的*/proc sql;create tablenew12 asselect age,count(*)asmanyfrom newgroup by age;proc sql;create tablenew13 asselect *from new12having manymax(many);quit;/*三、连接 难难难~~~~*//*简单合并用merge在data步*//*交叉合并cross join 、 full join(key)、naturefull join(key)inner join、leftjoin、right join笔记本上*/data one;do a1,2;output;end;run;data two;do b10,9,8;output;end;run;data combined;merge one two;run;proc sql;create tablecombined asselect *from one cross join two;quit; /*两种合并处理效果不一样*//*四、where exists、where in(where any)查询数据库中已有的观测*/data H;input name $age;cards;John 12;proc sql;create tableHH asselect *from Hwhere exists(select*from newwhere h.namenew.name);quit; /*有趣哈哈哈哈*/proc sql;create tableHHH asselect *from hwhere name in(select namefrom new);quit;proc sql;create tableHHHH asselect *from hwhere nameany /*补充ANY用法 any(20,30)相当于20 (select namefrom new);quit;/*五、intersect(交集)union(并集)expect(补集)余集(得另外编程没有特定的代码)、union all(全集)、的区别*//*补充ALL用法 all(20,30)相当于大于30/*any就是大于小的小于大的all就是小于小的大于大的*//*小结对小型表连接 数据步更有效其余肯定SQL更有效啦*//*六、去掉标题(title)显示程序效率 (stimer)*/TITLE;proc sql feedback stimer;select *from neworder byage;reset nostimer outobs3;select *from neworder byage;quit; /*厉害这样显示*//*SQL在几个主要程序测试与效率exec/noexec控制SQL语句是否在执行nostimer/stimer在sas日志中报告每个SQL语句的效率统计数据noerrorstop/errorstop批处理时控制当有错误发生时是否要检查语法*//*七、利用宏变量*/data w;input ID $ a1-a4;cards;A 11 12 13 14B 21 22 23 24;/*计算各个变量之和*/proc sql;create tablesums asselect sum(a1) as sum1,sum(a2) as sum2,sum(a3) as sum3,sum(a4) as sum4 /*记住这没有点号*/from W;quit;/*等价*/proc summary dataw;var a1-a4;output outsumss(drop_type__freq_)sumsum1-sum4;run;data sumsss;set w endlast;ARRAY _a{*}a1-a4; /*定义数组*/ARRAY _sum{*}sum1-sum4;keep sum1-sum4;do i1 to 4;_sum{i]_a{i};end;if last then output;run;/*变量更多时 就用宏*/%macro selectsums(maxindex);%do n1 %to maxindex;sum(an)as sumn%if n NE maxindex %then ,;%END;%mend selectsums;PROC SQL;create tablesumssss asselect %selectsums(maxindex4)from w;quit; /*完美*//*八、利用SQL生成报表*//*九、混合复杂操作*//*行列转置 常规的转置有proc transpose ..*/proc sql;create tableww asselect id,a1 as item,a1 as vlauefrom wunion allselect id,a2 as item,a2from wunion allselect id,a3 as item,a3from wunion allselect id,a4 as item,a4from w;quit;proc sql;create tableverticalsums asselect *from ww;group byitem;quit;by tt.
