|
Usually when you do a lookup, you want to keep records in the "main" file which don't match up with anything in the lookup
table. That calls for an outer join (a left or right rather than full, since you ordinarily don't want extra records from the
lookup table). That's fine, except an outer join can only involve two tables at a time.
The brute force solution is to just nest outer joins. I fired up MS Access and built a query via its interface (wizard, object
property edits, etc.) then switched to SQL view. This is what I got:
SELECT DISTINCTROW
Main.Info, Main.Code1, Main.Code2, Lookup1.Result1, Lookup2.Result12
FROM Lookup2 RIGHT JOIN
(Lookup1 RIGHT JOIN Main ON Lookup1.ID1 = Main.Code1)
ON Lookup2.ID2 = Main.Code2;
SAS SQL syntax is a little different, but this gives the idea.
This is with just two lookups. It could get very messy with more.
On Fri, 30 Mar 2001 14:09:44 -0500, Fehd, Ronald J. <rjf2@CDC.GOV> wrote:
>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;
|
