[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sas-l
Subject:    Re: SQL join of data + format table
From:       Sigurd Hermansen <HERMANS1 () WESTAT ! COM>
Date:       2001-03-30 21:13:56
[Download RAW message or body]

RDBMS's don't have the same implementation of formats that SAS has.  Let me
describe a few alternatives:

- put codes in DB, define SAS formats in an %inc file, and use SAS and
formats to report by formatted values;

- put codes in DB and store formats in subsidiary table.  In this case,
refer to a view of formatted values (the example that follows has many
extensions):

data fmts;
   input obs    FMTNAME: $6.   VALUE    @28 LABEL $char10. ;
cards;
 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;
data d;
  x=0;
  output;
  x=3;
  output;
run;
proc sql;
  select (select label from fmts where fmtname="CHK" and value=x) as x
  from d
  ;
quit;

- put short labels instead of codes in the DB and use views to summarize or
attach alternative labels.

SQL's "inline views" can point to data in other tables.  The recursive
nature of SQL views makes them very useful for mapping of any one value to a
corresponding value in a look-up table.  I know that you will see
immediately the potential for using SAS programs to write long and
repetitive SQL selects (such as the one in the example above) from
value-label tables.  Not so ugly if you program it!  Much like using
dictionary.tables and other metadata sources.

The mindset has to change a bit, but new challenges open up new
possibilities.  Sig

-----Original Message-----
From: Fehd, Ronald J. [mailto:rjf2@CDC.GOV]
Sent: Friday, March 30, 2001 2:10 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: SQL join of data + format table


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;

[prev in list] [next in list] [prev in thread] [next in thread]