SAS常用功能

%GLOBAL odb;

%LET odb=cowlife;

%GLOBAL path;

%LET path=netslifemiscow;

%GLOBAL schema;

%LET schema=tmrlifearc;

%GLOBAL user;

%LET user=netslifesas;

%GLOBAL password;

%LET password=;

 

libname netssas "/paic/hq/sasuserdata16/netssasdata" ;

设置数据压缩：

options compress=binary;

 

libname &odb oracle path=&path schema=&schema user=&user password="&password";

 

 

/*系统变量*/

 

%GLOBAL systemdate;

%LET systemdate=%sysfunc(datetime());

 

%GLOBAL yyyymm;

%LET yyyymm=201503;

 

%GLOBAL yyyymm_01;

%LET yyyymm_01=%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(inputn(&yyyymm.,yymmn6.)),-1)),yymmn6.));

%put &yyyymm_01.;

 

%GLOBAL yyyymm_02;

%LET yyyymm_02=%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(inputn(&yyyymm.,yymmn6.)),0)),yymmn6.));

%put &yyyymm_02.;

 

%GLOBAL yyyymm_03;

%LET yyyymm_03=%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(inputn(&yyyymm.,yymmn6.)),1)),yymmn6.));

%put &yyyymm_03.;

 

%GLOBAL yyyymm_04;

%LET yyyymm_04=%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(inputn(&yyyymm.,yymmn6.)),2)),yymmn6.));

%put &yyyymm_04.;

 

%GLOBAL yyyymm_05;

%LET yyyymm_05=%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(inputn(&yyyymm.,yymmn6.)),3)),yymmn6.));

%put &yyyymm_05.;

 

optionscompress=binary;

/*schema=&schema_sfzdata*/

libname &odb_sfzoraclepath=&path_sfz user=&user_sfzpassword="&password_sfz";

 

/*sas逻辑库连接*/

 

%GLOBAL netsyh;

%LET netsyh=netsdw2;

 

libname netsdw2base"/paic/hq/sasuserdata24/netsdw2";

LIBNAME CRM_XQDbase"/paic/hq/sasuserdata19/CRM_XQD";

 

 

 

 

时间函数，可以用在sql中，和sysdate一样使用

%GLOBAL systemdate;

%LET systemdate=%sysfunc(datetime());

 

建表：

proc sql noprint;

                create table &netssasdata..ods_telsales_fysp_ym_tmp

( ORDER_NO              VARCHAR(16),

  IS_PRIMARY_AGENT      VARCHAR(1)

);

quit;

 

建索引：

proc sql noprint;

CREATE UNIQUE INDEX ORDER_NO ON &NETSSASDATA..DW_LA_CYLCE_ORDER_MM_&DATA_MONTH(ORDER_NO);

quit;

 

设置输出集记录数1000

proc sql outobs=1000;

select t.campaign_id,t.campaign_name

,sum(1) as fst_call_cnt,sum(t.TOTAL_CALL_COUNT) as TOTAL_CALL_COUNT,

sum(t.TOTAL_CONTACT_COUNT) as  TOTAL_CONTACT_COUNT

from &netssasdata..DWA_LA_CYLCE_MM_201206 t

where t.campaign_month='201206'

and t.custlist_type_code='19'

group by t.campaign_id,t.campaign_name;

quit;

 

数据导入：

data &netssasdata..dim_telephone_property;

 input telephone_number_3$ telephone_sp_code telephone_sp$ is_3g @@;

 cards;

134 1 中国移动 0

130 2 中国联通 0

186 2 中国联通 1

133 3 中国电信 0

189 3 中国电信 1

 ;

run;

 

data合并

data &netssasdata..dw_la_cust_tel_mm_&data_month;

                set &netssasdata..dw_la_free_cust_tel_mm_&data_month;

                set &netssasdata..dw_la_free_cust_telh_mm_&data_month;

                set &netssasdata..dw_la_cylce_cust_tel_mm_&data_month;

                set &netssasdata..dw_la_cylce_cust_telh_mm_&data_month;

run;

 

数据排序

proc sort data = &netssasdata..dw_la_cust_tel_mm_&data_month;

                by telephone_number assigned_task_id;

run;

 

类似rownumber功能

data &netssasdata..dw_la_cust_tel_mm_seq_&data_month;

                set &netssasdata..dw_la_cust_tel_mm_&data_month;

                                by telephone_number;

                if first.telephone_number then seq=1;

                                else seq+1;

run;

 

筛选rn=1

proc sql noprint;

create table &netssasdata..dw_la_cust_tel_mm_seq_1_&data_month as

                select assigned_task_id,telephone_number

                  from &netssasdata..dw_la_cust_tel_mm_seq_&data_month a

                 where a.seq = 1

;

quit;