Javarang's life

filename ff pipe 'dir /p' ;

data _null ;
  infile ff;
  input ;
  put _infile_ ;
  a = _infile_ ;
run;

%macro tttt;
data _null_;
aa=pathname("work");
put aa=;
run;
%mend tttt;
%tttt;

/* work 전체 지움 */
proc datasets library=work kill; run;

/* work.aa 데이터셋 지움 */
proc datasets library=work nolist;
delete aa;
run;

proc sql;
connect to oracle (user=scott password=tiger);
create view budget2000 as select amount_b,amount_s
   from connection to oracle
   (select Budgeted, Spent from annual_budget);
quit;


proc sql;
   connect to oracle(user=scott password=tiger);
   execute (create view whotookorders as select ordernum, takenby,
     firstname, lastname,phone from orders, employees
     where orders.takenby=employees.empid) by oracle;
   execute (grant select on whotookorders to testuser) by oracle;
disconnect from oracle;
quit;

%let metaUser          = %nrquote(sasuserName);
%let metaPass          = %nrquote(sasPassword);
%let metaPort          = %nrquote(metadataPort);
%let metaServer        = %nrquote(metadataServerNameOrIP);
%let metaRepository    = %nrquote(Foundation);
%let metaTargetLibrary = %nrquote(targetLibraryName);

proc metalib;
  omr (       library="&metaTargetLibrary"
                 user="&metaUser"
             password="&metaPass"
           metaserver="&metaServer"
       metarepository="&metaRepository"
             metaport="&metaPort");
update_rule=(delete);
report;
run;

proc sql;
     CREATE TABLE DD AS
     select *
      from dictionary.members
      where libname in('CVSAG','CVSAP','CVSBN','CVSCZ','CVSDG','CVSES','CVSFS',
                       'CVSHS','CVSIS','CVSMK','CVSPT','CVSRO','CVSSW','CVSUK',
                       'CVSTOT','DAT_VAL');
quit;

%MACRO DIR_CRE (LIBNAME,MEMNAME,Z);
 
PROC CONTENTS NOPRINT DATA=&LIBNAME..&MEMNAME OUT=WORK.&MEMNAME;
RUN;

PROC SQL;
      CREATE TABLE BB AS
   SELECT LIBNAME, MEMNAME, COUNT(NAME) AS CNT
      FROM WORK.&MEMNAME
      GROUP BY LIBNAME, MEMNAME;
QUIT;

PROC APPEND BASE=WRSTEMP.GEN_TABLE DATA=WORK.BB FORCE;
RUN;

%MEND;

DATA _null_;
  if 1=1 then set WORK.DD nobs=obs;
    do z=1 to obs;
      set WORK.DD;
      call execute('%DIR_CRE ('||compress(LIBNAME)||','||compress(MEMNAME)||','||compress(z)||')');
    end;
RUN;

proc sql;

create table aaa as
select * from dictionary.tables;

quit;

proc sql;

create table aaa as
select * from dictionary.columns;

quit;

%macro exportToExcel(tname);
filename _temp_ "C:\Documents and Settings\Administrator\바탕 화면\Chart 문서정리\samples\&tname..xls";
ods noresults;
ods listing close;
ods html file=_temp_ rs=none style=minimal;
proc print data=&tname noobs;
run;
ods html close;
ods results;
ods listing;
filename _temp_;
dm "winexecfile ""C:\Documents and Settings\Administrator\바탕 화면\Chart 문서정리\samples\&tname..xls"" ";
%mend;

%exportToExcel(REPORT.Flex2_xxx);

상황에 따라 다르겠지만....

/* 실행시간 : 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
*
********************************************