/* Below SAS codes are used as training material in a one-day training  */
/* class to train engineers in writing their own SAS programs to access */
/* and analyze engineering data in company's sites.                     */                                  */

/*****************************************************/
/* SAS code to access Ingres database for PT data.   */
/* Then, a summary report is generated.              */
/*          Jeff Yuan   9/23/98                      */
/*****************************************************/
proc sql;
connect to INGRES as sqlb_cid (DATABASE=PHX005::IDEA_DB);
create table lot_pt (label = 'no description') as
    select * from connection to sqlb_cid
    (select distinct
            l.product, l.lot, l.t84_test_date as date, l.t84_lot_ref, d.wafer_no,
            d.value_1, d.value_2, d.value_3, d.value_4, d.value_5, d.value_6,
            d.value_7, d.value_8, d.value_9,
            s.parameter as para, s.parameter_no, s.usl, s.lsl, s.target, s.uvl, s.lvl
     from
            t84_lot l, t84_data d, t84_specs s
     where
            l.t84_lot_ref=d.t84_lot_ref and l.t84_spec_ref=s.t84_spec_ref
        and d.parameter_no=s.parameter_no
        and l.lot='P002831'
     );
disconnect from sqlb_cid;
quit;
/*
proc print data=lot_pt (obs=200);
run;
*/
data some; set lot_pt (drop=value_6 - value_9 paramete date);
     where para in ("RSM1","RSNW_F", "NVT10045");
proc sort data=some; by para product;
proc print data=some;
run;

/*** Transpose the data ***/
data value; set some (drop=para usl lsl target uvl lvl);
proc sort data=value; by product lot t84_lot_ wafer_no;
proc transpose data=value
     out=final1 (drop=_label_ rename=(_name_=site));
     by product lot t84_lot_ wafer_no;
run;
proc print data=final1;
run;

proc means data=final1;
var col1 col2;
run;

data spec; set some (drop=value_1 - value_5);
proc sort data=spec; by product lot t84_lot_ wafer_no;
run;

/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/

/****************************************************************/
/* The SAS program accesses a view called chrono in IDEA/WS2    */
/* and to generate a report for commonality analysis.           */
/*                                                              */
/*                          Jeff Yuan    8/7/98                 */
/****************************************************************/

proc sql;
Connect to ingres as mycon (database = "phx005::idwsphx");
create table prctmp as
select  * from connection to mycon (
  select *  from chrono
  where (
/******* selections you can change or remove *******/
  (testdate between '01-JAN-2000 00:00:00'
                and '14-MAR-2000 00:00:00')
  and  /*
  (route like 'HC5%')
  and
  (nm_eqt like 'ST%')
  and
  (part = 'FNL05A03')
  and  */
  ((lot='P001599') or
   (lot='P002690') or
   (lot='P002689') or
   (lot='P002733'))
)
disconnect from mycon;
quit;

options ps=80 ls=128 byline ovp;

/*** exclude some data. ***/
proc sort data=prctmp;
  where index(event,'#')=1 and substr(event,1,7) ne '#DEVICE' and substr(event,1,4) ne '#DVC';
  by phase event lot route;
/*** eliminate duplicates. ***/
data prctmp; set prctmp; by phase event lot;
  if last.lot;

proc sort;
  by phase event testdate;
proc print data=prctmp width=min;
  var part lot nm_eqt testdate wafers phase event;
  id route;
  by phase;
run;

/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/

/**************************************************************/
/* SQL to perform a query against the ORACLE (Firms) database */
/* and then generate a Hardbin report.                        */
/*                                                            */
/* Author: Jeff Yuan   (Ex. 2867)                             */
/*         09/10/98                                           */
/**************************************************************/
options ps=55 ls=85;
%let lot = p002831;

/*** Access to FIRMS database. ***/
proc sql;
  connect to oracle as sqltab (user=visitor password=visitor path ="@t:sun4sx22:F");
  create table ortable0 (label = 'no description') as
   select * from connection to sqltab
       (select a.lot_id,  a.sblot_id, a.part_cnt, a.good_cnt, a.master_id,
               b.bin_num, b.bin_cnt,  b.site_num, b.site_grp, b.bin_pf, c.wafer_id
        from firms.master_info a, firms.hard_bin b, firms.wafer_info c
        where lot_id = %str(%'&lot%')
          and a.master_id = b.master_id
          and b.master_id = c.master_id
        );
  disconnect from sqltab;
quit;

data ortable1;
  set ortable0 (keep=lot_id wafer_id part_cnt good_cnt bin_num bin_cnt);

data wafer1;
  set ortable1 ; where wafer_id='01';
run;

/*** Input bin number here ***/
data lookup;
  input bin_num @@;
  cards;
  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
  ;

data lookup;
  if _n_=1 then set wafer1 (keep=lot_id part_cnt good_cnt wafer_id);
  set lookup;

data wafer01;
  merge wafer1 (keep=bin_num bin_cnt) lookup (in=b);
  by bin_num;
  if b;
run;

data wafer;
  set ortable1; where wafer_id ne '01';

data ortable;
  set wafer wafer01;
run;

proc sort data=ortable;
     by lot_id wafer_id bin_num;

/*** Transpose the data table. ***/
proc transpose data=ortable out=binfile prefix=bin;
     id bin_num;
     var part_cnt good_cnt bin_cnt;
     by lot_id wafer_id;
run;

data bin (drop=_name_ _label_);
    set binfile;
    if _name_='BIN_CNT';
data good (rename=(bin1=good) drop=_name_);
    set binfile (keep=lot_id wafer_id _name_ bin1);
    if _name_='GOOD_CNT';
data part (rename=(bin1=part) drop=_name_);
    set binfile (keep=lot_id wafer_id _name_ bin1);
    if _name_='PART_CNT';

data final;
    format lot_id $char10. wafer_id $char3. good part bin1-bin15 4.0;
    merge good part bin;
    by lot_id wafer_id;

proc print data=final;
     id wafer_id;
run;

/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/

/*************************************************************/
/*   SAS code for reading data into SAS from external file.  */
/*   MACRO concept is introduced.                            */
/*                Jeff Yuan   9/24/98                        */
/*************************************************************/

/*** pt_data.txt is an external data file. ***/
options ls=80 ps=55;
data april;
 infile '~/sas_train/pc_data.txt';
 input  RF $ UW $ PP $ IM $ OM $ pm2mean pm2range pm2std td4mean td4range td4std;
 if _n_=8 then delete;

proc print data=april;
run;

/*** SAS code for DOE analysis. ***/
%macro doe(resp);
proc glm data=april;
  title "DOE Analysis on &resp";
  class RF UW PP IM OM;
  model &resp = RF UW PP IM OM;
  means RF UW PP IM OM/tukey;
run;
%mend doe;

%doe (pm2mean)
run;
%doe (pm2range)
run;
%doe (pm2std)
run;
%doe (td4mean)
run;
%doe (td4range)
run;
%doe (td4std)
run;

/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/

/********************************************************/
/* Title: Photo CD Analysis                             */
/* Purpose: Generate Trend Chart by Tool or by product  */
/* Author:  Jeff Yuan  (X2867)    10/5/98               */
/* Modifications:    new db data structure  06/08/99    */
/********************************************************/

/***********************************************************************************/
/*  Description:                                                                   */
/*  This program accesses a SAS data set extracted daily by SAS/SQL.               */
/*  It will generate the trend chart for Photo CD measurements.                    */
/*                                                                                 */
/*  SETUPS:                                                                        */
/*   param    - the name of PARAMETERS, such as BOTTOM15, CD_RANGE_, CDMEAN.       */
/*              Required.                                                          */
/*   event    - the name of event, such as _CDHC51700, GETCD5L4170.                */
/*              Required.                                                          */
/*   by_var   - product or equipment, which defines the by-variable for the chart. */
/*              Required.                                                          */
/*   multi_ln - YES or NO, which determines that multiple lines are used to link   */
/*              data points or just one line to link all data points on the chart. */
/*              Required.                                                          */
/*   stepper  - the name of stepper/equipment, such as ST01, ST02.                 */
/*              select one stepper or leave it blank for all steppers.             */
/*   product  - the name of product, such as F2TP2C3P.                             */
/*              select one product or leave it blank for all products.             */
/*   begin    - the beginning of the specified period.                             */
/*   end      - the end of the specified period. The max length is 60 days         */
/*                                                                                 */
/***********************************************************************************/

%let param=bottom9;
%let event=_CDHC51700_;
%let by_var=product;
%let multi_ln=yes;
%let stepper=;
%let product=;

%let begin=03AUG1999:00:00:00;
%let end=03SEP1999:00:00:00;
/*******************************/
%macro option;
   %if &stepper ^= %str() and &product = %str() %then %do;
     if equipmnt="%upcase(&stepper)";  %end;
   %else %if &product ^= %str() and &stepper = %str() %then %do;
     if product="%upcase(&product)"; %end;
   %else %if &product ^= %str() and &stepper ^= %str() %then %do;
     if product="%upcase(&product)";
     if equipmnt="%upcase(&stepper)";
   %end;
%mend option;

%macro op_var;
  %global byvar;
  %if %upcase(%substr(&by_var,1,4))=PROD %then %do;
     %let byvar=product; run;
  %end;
  %else %do;
     %let byvar=equipmnt; run;
  %end;
  %put &byvar;
%mend op_var;

%macro op_event;
  %global byvar;
  %if %upcase(&param)=CDMEAN or %upcase(&param)=CDHI or %upcase(&param)=CDLO or
      %upcase(&param)=BOTTOM15 or %upcase(&param)=BOTTOM9 or
      %upcase(&param)=RATIO15 or %upcase(&param)=RATIO9 %then %do;
        %ycpk1(indata=new, stats=tep, var=&byvar);
  run;
  %end;
  %else %do;
     %ycpk2(indata=new, stats=tep, var=&byvar);
     run;
  %end;
%mend op_event;

options nodate nonumber nocenter ps=90 ls=120;
/**************************/
/* point to the data set. */
/**************************/
libname photocd '~phxtools/engdb/pi2/raw';
%yaxis (indata=photocd.photo, outdata=new1);
run;

data new2;
  set new1 (keep=param product equipmnt prompt event usl lsl value parametr date lot_id);
  if event="%upcase(&event)";
  if param="%upcase(&param)";
  if value=. then delete;
  if  "&end"dt > date > "&begin"dt;
  %option;
run;

proc sort data=new2;
  by lot_id date;
run;

data new;    /* remove rework data */
  set new2;
  by lot_id;
  if last.lot_id;
run;

/*
proc print data=new (drop=date);
  title "Photo CD Analysis for &event";
  footnote;
run;
*/

/********************************************/
/* generate a text file for the statistics. */
/********************************************/
%op_var; run;
filename summ '~/temmp';
proc printto print=summ new;
run;
%op_event;
run;

options ps=18 ls=64;
proc sort data=tep; by descending n ;
run;
proc print data=tep (obs=15);
  id &byvar;
  title; footnote;
run;
proc printto;
run;

/**********************************/
/* gprint and gplot the graph.    */
/**********************************/
%ycdgraph(indata=new, var=%upcase(&byvar), oneline=%upcase(&multi_ln));
run;

proc GREPLAY nofs igout=work.photo;
  delete _all_;
run; quit;

/*******************************************/
/* convert the graph to PS file.  Optional */
/*******************************************/
/*
%yreply(base=~/,gname=cdphoto, gdev=pscolor, library=work, catalog=gseg);
run;
*/

/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/

/*****************************************************/
/*  SAS code using the dataset extracted by EngWs    */
/*  to generate customized Boxplot and Histogram.    */
/*           Jeff Yuan      9/24/98                  */
/*****************************************************/

/** Point to the SAS dataset that was generated by EngWs. **/
libname pi2raw '~yuanj/engdb/pi2/raw';

/** Manipulate the data. **/
data new; set pi2raw.engdata;
  if prompt='BLANK' then pprompt='0'; else pprompt='1';
  h=hour(timepart(date));
  m=minute(timepart(date));
  s=second(timepart(date));
  if h<10 then hh=compress('0'||h); else hh=h;
  if m<10 then mm=compress('0'||m); else mm=m;
  if s<10 then ss=compress('0'||s); else ss=s;
  if value < -100 then value=.;         /* filter out the outliers. */
  if value > 0 then value=(-1)*value;
     else value=value;
  dday=compress(substr(left(day),5,4)||':'||hh||':'||mm||':'||ss);
  parametr=compress(dday||'/'||pprompt);
run;
proc sort data=new;
by equipmnt event param parametr;

goptions reset=ALL device=xcolor targetdevice=cljps
         gaccess=sasgastd gsfname=gsasfile gsfmode=replace vpos=60 hpos=100
         cback=white rotate=landscape;
filename gsasfile pipe "lpr -d$PRINTER -oraw";

/*** Using gplot to generate trend charts. ***/
symbol1 i=none font=marker v=P h=1.0 c=black ;
proc gplot data=new;
  plot value*parametr / grid frame;
  by equipmnt event param;
  where param='VFB';
run;quit;

/*** Generate a Histogram.  ***/
title h=3.5 f=duplex "Capability Analysis";
footnote1 h=1.5 font=simplex j=r "&sysdate,  &systime";

proc capability data=new graphics noprint;
     spec lsl=-1.2 llsl=2 usl=-0.5 lusl=2;
     histogram value /normal cfill=ligr font=duplex cbarline=black
                      nospeclegend nolegend href=-1.2 -0.5
                      lhref=3 chref=red vaxis=axis1 haxis=axis2
                      ;
     inset n mean std='STD' Cp Cpk lsl usl pnormal='p-value' pctbet='% in spec'/
           format=5.2 cfill=blank pos=RM height=2.2 font=duplex header='Summary';
     axis1 value=(h=2.2 j=r font=duplex) label=(h=2.5 font=duplex j=r 'Percent') ;
     axis2 label=(h=2.5 font=duplex 'VF01') value=(h=2.2 j=c font=duplex );
     where equipmnt = 'VF01';
run; quit;


/** Convert the Histogram to a CGM file which  **/
/** can be read by Lotus 1-2-3.                **/
options mautosource ls=90 ps=60 ovp byline nonumber;
%ylotus (file=~/sas_train/mycgm.cgm, catalog=work.gseg, plot=capabili);
run;                     /* FILE is the path and the CGM file name. */
                         /* CATALOG is the SAS graphic lib.         */
                         /* PLOT is the praph you want to convert.  */

/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/

/******************************************************/
/*  Example in using goption to define the SAS graph  */
/*  and setup the printer device. Proc gplot is used. */ 
/*       Jeff Yuan      10/16/98                      */
/******************************************************/
goptions reset=all border colors=(black) ftext=swiss
device=xcolor targetdevice=dj1600c
gaccess=sasgastd gsfname=gsasfile gsfmode=replace;

filename gsasfile pipe "lpr -d$PRINTER -onobanner";

data one;
 input lot $4. yield;
 cards;
lot1 99
lot2 98
lot3 89
lot4 90
lot5 95
;
run;
proc print data=one;
run;

data addn;
 set one;
 lotord=_n_;
run;

data stname;
 set addn(rename=(lotord=start lot=label));
 fmtname='dataord';
 type='N';
 keep fmtname label start type;
run;
proc print data=stname;

proc format cntlin=stname;

title1 f=swissb 'yield by lot';
symbol1 i=none f=swissb v='$' h=4 c=green;
symbol2 i=none v=none;
axis1 order=(0 to 100 by 10) offset=(0,0)
      value=(f=swissb) label=none;
axis2 minor=none offset=(3,3) value=(f=swissb h=1)
      label=none;

proc gplot data=addn;
 plot yield*lotord / vaxis=axis1 autovref haxis=axis2;
 plot2 yield*lotord / vaxis=axis1 haxis=axis2;
 format yield dollar8. lotord dataord.;
run;
quit;

/*
options nonumber nodate nocenter;
filename test '~/sas_train/mytest.txt';
proc printto print=test; run;

proc print data=one;
title;
run;
proc printto; run;
*/