SQL Dilemma

SQL Dilemma

Post by Jacinto F Mendo » Tue, 27 Feb 2001 07:06:40



  HI!  I'm attempting to create the following tables from sql:

(first table)
row_main        row_number       19-24_pct        7-18_pct
7-24_pct
----------        -------------        ----------        ----------
 ----------
A                             1                        1                4
                       4
                                2                        6
16                      16
                                3                        12
51                      26
                                4                        21
29                      20
                                5                        57
0                        32

(second table)
row_main        row_number       19-24_pct        7-18_pct
7-24_pct
----------        -------------        ----------        ----------
 ----------
B                             1                        11
40                        4
                                2                        66
 61                      16
                                3                        21
 15                      62
                                4                        12
 92                      2
                                5                        75
 10                      23

AND then I would like to join these two tables to look like this IN ONE
MAIN TABLE:

row_main        row_number       19-24_pct        7-18_pct
7-24_pct
----------        -------------        ----------        ----------
 ----------
A                             1                        1                4
                       4
                                2                        6
16                      16
                                3                        12
51                      26
                                4                        21
29                      20
                                5                        57
0                        32
B                             1                        11
40                        4
                                2                        66
 61                      16
                                3                        21
 15                      62
                                4                        12
 92                      2
                                5                        75
 10                      23

I've checked out the SAS procedures guide V8 but I'm having problems
producing the desired output (any ideas would be great! ... Please
Help!).  Here's the tentative syntax of the program (for one table which
I'll later duplicate to produce the second table, then I'll create
another sql procedure (to join the two tables) to create the TABLE THAT I
REALLY WANT):
proc sql number;
        create table jackson as
        select row_main,
        dur_rec as row_number,
        sum(empl_1924)/1069 as 19-24pct format=percent6.,
        sum(emp_718)/560 as 7-18pct format=percent6.,
        sum(employ1924)+sum(emp_718) as curr_employ,
        (calculated curr_employ/1924) as 7-24_pct format=percent6.,
        from labour1
        group by row_main;
        select * from jackson;

 
 
 

SQL Dilemma

Post by Andreas Gruening » Tue, 27 Feb 2001 18:08:56


Dear Jacinto,

Please post your error logs.
Otherwise it is too  much speculation about the real errors.

I speculate  you want to combine results of a bootstrapping process.
And the method to prdouce the layout of your desired final result
table is that what old fashioned SAS users would name as INTERLEAVING
and not as JOINING.
The main difference is that INTERLEAVING does not combine records of
two or more tables in ONE record of the output table but appends the
records.
And this is very efficiently done with PROC APPEND.
Especially in bootstrapping is this of great importance concerning the
overall runtime.

Before the starting the bootstrapping you create an empty table, with
SQL or a DATA STEP. Let us name it 'resultsFinal'.

* start of bootstrapping ;

* In the bootstrapping you make your calculations and create a table
with the same structure like 'resultsFinal' with your SQL statement
==> your SQL statement  ;

        create table resultsRound as
        select row_main,
        dur_rec as row_number,
        sum(empl_1924)/1069 as pct_19_24 format=percent6.,
        sum(emp_718)/560 as pct_7_18 format=percent6.,
        sum(employ1924)+sum(emp_718) as curr_employ,
        (calculated curr_employ/1924) as pct_7_24 format=percent6.,
        from labour1
        group by row_main;
* or for * SQL freaks, remove the following PROC APPEND if you
use this one ;
*   INSERT INTO resultsFinal SELECT * FROM resultsRound;      
QUIT;

PROC APPEND BASE=resultsFinal DATA=resultsRound;
RUN;            

* end of this round ;

Quote:>producing the desired output (any ideas would be great! ... Please
>Help!).  Here's the tentative syntax of the program (for one table which

* --------------------------------- +
! Ingenieurbuero Grueninger         !
! Andreas Grueninger                !
! Uhlbergstr. 15                    !
! 72631 Aichtal (Germany)           !

+ --------------------------------- ;

 
 
 

1. SQL dilemma

Suppose I've got two DB2 tables, which I will simulate here using SAS
datasets for the moment (the actual tables contain millions of records):

data test1;
input ID;
cards;
1234
5678
9876
5432
;
run;

data test2;
input ID;
cards;
1234
9876
7777
;
run;

I need to find the count of unique IDs across both tables; that is to say, I
need to count every distinct value of ID only once, without double (or
triple, quadruple, etc) counting any id that appears in both tables.

So, I'd expect the resulting count from the simulated tables above to be 5,
or, if I were to get the actual IDs instead of the count:

1234
5678
9876
5432
7777

So far the best thing I've hit on (below) seems to produce the list of IDs I
expect, but I think it is very inefficient as it involves two Cartesian
joins.  Also, I haven't quite worked out how to get the count of the IDs
rather than the IDs themselves from this query.

proc sql;

create table test3 as

select a.id from test1 a,
                       test2 b
where a.id ne b.id

union

select b.id from test1 a,
                       test2 b
where b.id ne a.id;

quit;

The query will really be done in a pass-through SQL to DB2, so whatever I do
has to be valid DB2 SQL and I need to handle it on the DB2 side rather than
pull both multi-million row tables into SAS to do it in a data step.

Can anyone suggest a better approach?

Thanks,

Sterling Price

**********************************************************************
Notice:  The area code for the Wal-Mart General Office has
changed from 501 to 479.  Please make sure that you are
dialing 479 when making calls to any General Office location.

**********************************************************************
This email and any files transmitted with it are confidential
and intended solely for the individual or entity to
whom they are addressed.  If you have received this email
in error destroy it immediately.
**********************************************************************

2. Need help with Office 2k, ASAP Pls

3. SQL dilemma and gen'l Sas-L question

4. SUPER-PC

5. 70-029 (SQL Server) vs Oracle DBA SQL PL/SQL.

6. datacad

7. XML to *ML

8. umax1220s/diff scsi card dilemma

9. New Dilemma

10. HUGE dilemma..... respond quick

11. Broadband Dilemma

12. X-10 Inc. Warranty dilemma.