Date:         Fri, 30 Mar 2001 14:09:44 -0500
Reply-To:     "Fehd, Ronald J." <rjf2@CDC.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Fehd, Ronald J." <rjf2@CDC.GOV>
Subject:      SQL join of data + format table
Content-Type: text/plain; charset="iso-8859-1"

I have the following problem:
in the context of designing tables for storage in an SQL database
I would like to understand what structure is appropriate for storage of SAS
formats.

I have dumped my format library to a data set and renamed the variables,
see test data below .sig.
Now I am looking for the pattern of SQL statements to join each item of the
format data set
to the appropriate variable.

While this is a piece of cake in SAS:
data WITH_FORMATS;
 set TEST;
 Mnfr_Label = put(Mnfr,$mnfr.);
 Qc1_label  = put(QC1 ,$chk.);
 Qc2_label  = put(QC2 ,$chk.);
                                     MNFR_      QC1_     QC2_
Obs    ID    MNFR    QC1    QC2      LABEL      LABEL    LABEL

 1      1     01      1      0     Mnfr One      Yes      No
 2      2     04      0      1     Mnfr Four     No       Yes
 3      3     02      1      0     Mnfr Two      Yes      No
 4      4     02      0      1     Mnfr Two      No       Yes
 5      5     04      1      0     Mnfr Four     Yes      No

What I want is the SQL that produce this result.

tia

Ron Fehd  the macro maven  CDC Atlanta GA USA RJF2@cdc.gov
OpSys: WinNT   Ver: 8.1
remember perspective: the _join_ is not always where it seems to occur! --
RJF2


data TEST;
length ID     4
       Mnfr $ 2
       QC1
       QC2  $ 1
       ;
do ID = 1 TO 5;
   Mnfr = put(mod(ID**2,7),z2.);%*returns 01 04 02 04;
   QC1 = put(    mod(ID,2),1.);
   QC2 = put(not mod(ID,2),1.);
   output;end;
stop;
proc PRINT;
/******************************
Obs    ID    MNFR    QC1    QC2

 1      1     01      1      0
 2      2     04      0      1
 3      3     02      1      0
 4      4     02      0      1
 5      5     04      1      0
/*****************************/
proc FORMAT cntlout = FORMAT
            (keep   = FmtName Start Label
             rename = (Start = Value));
value $Mnfr
      '01' = 'Mnfr One  '
      '02' = 'Mnfr Two  '
      '03' = 'Mnfr Three'
      '04' = 'Mnfr Four '
      ;
value $chk
      '0' = 'No '
      '1' = 'Yes'
      ;
proc PRINT;
/*************************************
Obs    FMTNAME    VALUE    LABEL

 1      CHK        0       No
 2      CHK        1       Yes
 3      MNFR       01      Mnfr One
 4      MNFR       02      Mnfr Two
 5      MNFR       03      Mnfr Three
 6      MNFR       04      Mnfr Four
/************************************/
run;