Javarang's life

/* 실행시간 : 5.12sec */
proc sql;
create table target as
select distinct
       tname
     , cname
     , sum(cnt) as scnt
 from code_freq_master
 group by tname, cname;

create table code_freq_master1 as
select a.yyyymmdd
     , a.tname                                    label="테이블명"
     , a.cname
     , a.vname
     , a.cnt
     , (a.cnt/b.scnt)*100   as cratio format=6.2  label="분포비율"
  from target as b
 inner join code_freq_master a
         on a.tname = b.tname
        and a.cname = b.cname;
quit;
/* 실행시간 : 0.26 + 3.25 + 0.03 + 2.50 = 6.04 */
proc means data=code_freq_master noprint;
       var cnt;
     class tname cname;
    output out=tmp1(where=(_type_=3) ) sum=sum;
run;
proc sort data=code_freq_master ; by tname cname; run;
proc sort data=tmp1 ; by tname cname; run;
data code_freq_master2;
merge code_freq_master(in=in1) tmp1(in=in2 drop=_type_ _freq_);
   by tname cname;
      percent=cnt/sum*100;
   if in1 and in2;
label percent='분포비율'
      sum='합계';
run;


********************************************
*
* Do your best!!
*
* Homepage : http://www.javarang.net
* Javarang Lee
*
********************************************

%macro importData(filename);
data &filename;
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
infile "c:\SyBase\Code\&filename..txt" delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=1 ;

format yyyymmdd $ 8. ;
format tname $ 24. ;
format cname $ 48. ;
format vname $ 400. ;
format cnt 18. ;

informat yyyymmdd $ 8. ;
informat tname $ 24. ;
informat cname $ 48. ;
informat vname $ 400. ;
informat cnt 18. ;

input
 yyyymmdd $
 tname $
 cname $
 vname $
 cnt
;       
if _ERROR_ then call symput('_EFIERR_',1);  /* set ERROR detection macro variable! */
run;
%mend;

%importData(table1);

********************************************
*
* Do your best!!
*
* Homepage : http://www.javarang.net
* Javarang Lee
*
********************************************

/*
PROC MEANS ;
        BY variable-1 <... variable-n>; 
     CLASS variable(s)
           ; 
      FREQ variable; 
        ID variable(s); 
    OUTPUT
          
          
          
          
           ; 
     TYPES request(s); 
       VAR variable(s)
           < / WEIGHT=weight-variable>; 
      WAYS list; 
    WEIGHT variable; 

BY     : Calculate separate statistics for each BY group
CLASS  : Identify variables whose values define subgroups for the analysis
FREQ   : Identify a variable whose values represent the frequency of each observation
ID     : Include additional identification variables in the output data set
OUTPUT : Create an output data set that contains specified statistics and identification variables
TYPES  : Identify specific combinations of class variables to use to subdivide the data
VAR    : Identify the analysis variables and their order in the results
WAYS   : Specify the number of ways to make unique combinations of class variables
WEIGHT : Identify a variable whose values weight each observation in the statistical calculations

/*
PROC CONTENTS <option(s)>;

CENTILES          : Print centiles information for indexed variables
DATA=             : Specify the input data set
DETAILS|NODETAILS : Include information in the output about the number of observations, number of variables, and data set labels
DIRECTORY         : Print a list of the SAS files in the SAS data library
FMTLEN            : Print the length of a variable's informat or format
MEMTYPE=          : Restrict processing to one or more types of SAS file
NODS              : Suppress the printing of individual files
NOPRINT           : Suppress the printing of the output
ORDER=IGNORECASE  : Print a list of variables in alphabetical order even if they include mixed case names
OUT=              : Specify the output data set
OUT2=             : Specify an output data set that contains information about constraints
SHORT             : Print abbreviated output
VARNUM            : Print a list of the variables by their logical position in the data set

*/
proc contents data = sashelp.prdsal2
              noprint
              out = proc_contents;
run;

********************************************
*
* Do your best!!
*
* Homepage : http://www.javarang.net
* Javarang Lee
*
********************************************

/* Oracle Library setting */
libname library_name oracle readbuff=2500 direct_exe=delete path=dbname schema=user_id user=user_id password="userpassword"

/* MS Access Library setting */
libname library_name access 'file full path'

/* DBF */
filename dataset_name "file full path";
proc dbf db3=dataset_name out=lib_name.dataset_name;
run;

/* ODBC */
libname library_name odbc datasrc=data_source_name user=user_id password=user_password;


********************************************
*
* Do your best!!
*
* Homepage : http://www.javarang.net
* Javarang Lee
*
********************************************

proc univariate data=source_dataset noprint;
 var source_dataset_column;
 output out=target_dataset pctlpre=P_ pctlpts=init_value to max_value by interval_value NMISS=miss mean=av;
run;

source_dataset : 소스 테이블(라이브러리명 포함)
source_dataset_column : Percentile을 구할 column
target_dataset : 결과를 저장할 테이블(라이브러리명 포함)
init_value : 최초로 보여줄 값
max_value : 마지막으로 보여줄 값
interval_value : 분할할 개수

Ex>>
proc univariate data=WORK.V_FACT_TEMP noprint ;
 var R_COL;
 output out=tmp1 pctlpre=P_ pctlpts=20 to 100 by 20 NMISS=miss mean=av;
 run;

********************************************
*
* Do your best!!
*
* Homepage : http://www.javarang.net
* Javarang Lee
*
********************************************