Could you explain a little more about what your data looks like? Here is what the code would be for a simple substitution from one table to another:

data oldids;

infile cards;

input idfield2 matchfield;

cards;

1 100

2 200

;

data unmatched;

infile cards;

input idfield2 matchfield;

cards;

. 100

. 200

;

PROC SQL NOPRINT;

UPDATE unmatched

SET idfield2=(SELECT idfield2

FROM oldids

WHERE unmatched.matchfield EQ oldids.matchfield);

QUIT;

But it looks like you've got multiple rows in either the unmatched or the oldids table, and that's why you are including the idfield3. Would you work up some data similar to above, but showing where field3 comes into play?

-Mary

----- Original Message ----- From: Laptop765@GMAIL.COM To: SAS-L@LISTSERV.UGA.EDU Sent: Monday, May 19, 2008 2:34 PM Subject: Re: Nested Data Accesses?

On May 19, 2:59 pm, mlhow...@avalon.net (Mary) wrote: > Laptop765, > > I think that you are getting at the "calculated" keyword in SQL. Here = > is an example below; note at the very end there's code like: > having > (calculated eligible_day_30=3D1 and=20 > > -Mary > > =20 > data claims; > > input mbrid discharge mmddyy11.; > > format discharge date9.; > > datalines; > > 111 01/27/2008 > > 123 01/05/2008 > > 456 01/03/2008 > > 789 01/06/2008 > > 789 01/31/2008 > > 888 01/04/2008 > > 999 01/20/2008 > > ; > > run; > > data eligibility; > > input mbrid effective mmddyy11. term mmddyy11.; > > format effective term date9.; > > datalines; > > 111 01/01/2008 01/31/2008 > > 111 02/01/2008 02/29/2008 > > 111 03/01/2008 12/31/2008 > > 111 01/01/2008 12/31/2099 > > 123 01/01/2008 01/31/2008 > > 123 04/01/2008 12/31/2099 > > 456 01/01/2008 02/29/2008 > > 456 03/01/2008 03/31/2008 > > 789 01/01/2008 01/31/2008 > > 789 02/01/2008 02/29/2008 > > 888 03/01/2007 01/31/2008 > > 888 03/01/2008 12/31/2008 > > 999 01/01/2008 01/31/2008 > > ; > > run; > > proc sql; > > create table new as > > select claims.mbrid, discharge, effective, term,=20 > > case when (effective <=3D discharge + 30) and (term >=3D discharge + 30) = > then 1 else 0 end as eligible_day_30, > > case when (effective <=3D discharge + 29) and (term >=3D discharge + 29) = > then 1 else 0 end as eligible_day_29, > > case when (effective <=3D discharge + 28) and (term >=3D discharge + 28) = > then 1 else 0 end as eligible_day_28, > > case when (effective <=3D discharge + 27) and (term >=3D discharge + 27) = > then 1 else 0 end as eligible_day_27, > > case when (effective <=3D discharge + 26) and (term >=3D discharge + 26) = > then 1 else 0 end as eligible_day_26, > > case when (effective <=3D discharge + 25) and (term >=3D discharge + 25) = > then 1 else 0 end as eligible_day_25, > > case when (effective <=3D discharge + 24) and (term >=3D discharge + 24) = > then 1 else 0 end as eligible_day_24, > > case when (effective <=3D discharge + 23) and (term >=3D discharge + 23) = > then 1 else 0 end as eligible_day_23, > > case when (effective <=3D discharge + 22) and (term >=3D discharge + 22) = > then 1 else 0 end as eligible_day_22, > > case when (effective <=3D discharge + 21) and (term >=3D discharge + 21) = > then 1 else 0 end as eligible_day_21, > > case when (effective <=3D discharge + 20) and (term >=3D discharge + 20) = > then 1 else 0 end as eligible_day_20, > > case when (effective <=3D discharge + 19) and (term >=3D discharge + 19) = > then 1 else 0 end as eligible_day_19, > > case when (effective <=3D discharge + 18) and (term >=3D discharge + 18) = > then 1 else 0 end as eligible_day_18, > > case when (effective <=3D discharge + 17) and (term >=3D discharge + 17) = > then 1 else 0 end as eligible_day_17, > > case when (effective <=3D discharge + 16) and (term >=3D discharge + 16) = > then 1 else 0 end as eligible_day_16, > > case when (effective <=3D discharge + 15) and (term >=3D discharge + 15) = > then 1 else 0 end as eligible_day_15, > > case when (effective <=3D discharge + 14) and (term >=3D discharge + 14) = > then 1 else 0 end as eligible_day_14, > > case when (effective <=3D discharge + 13) and (term >=3D discharge + 13) = > then 1 else 0 end as eligible_day_13, > > case when (effective <=3D discharge + 12) and (term >=3D discharge + 12) = > then 1 else 0 end as eligible_day_12, > > case when (effective <=3D discharge + 11) and (term >=3D discharge + 11) = > then 1 else 0 end as eligible_day_11, > > case when (effective <=3D discharge + 10) and (term >=3D discharge + 10) = > then 1 else 0 end as eligible_day_10, > > case when (effective <=3D discharge + 09) and (term >=3D discharge + 09) = > then 1 else 0 end as eligible_day_09, > > case when (effective <=3D discharge + 08) and (term >=3D discharge + 08) = > then 1 else 0 end as eligible_day_08, > > case when (effective <=3D discharge + 07) and (term >=3D discharge + 07) = > then 1 else 0 end as eligible_day_07, > > case when (effective <=3D discharge + 06) and (term >=3D discharge + 06) = > then 1 else 0 end as eligible_day_06, > > case when (effective <=3D discharge + 05) and (term >=3D discharge + 05) = > then 1 else 0 end as eligible_day_05, > > case when (effective <=3D discharge + 04) and (term >=3D discharge + 04) = > then 1 else 0 end as eligible_day_04, > > case when (effective <=3D discharge + 03) and (term >=3D discharge + 03) = > then 1 else 0 end as eligible_day_03, > > case when (effective <=3D discharge + 02) and (term >=3D discharge + 02) = > then 1 else 0 end as eligible_day_02, > > case when (effective <=3D discharge + 01) and (term >=3D discharge + 01) = > then 1 else 0 end as eligible_day_01, > > case when (effective <=3D discharge + 00) and (term >=3D discharge + 00) = > then 1 else 0 end as eligible_day_00 > > from claims > > join eligibility > > on claims.mbrid=3Deligibility.mbrid; > > quit; > > run;=20 > > proc sql; > > create table new2 as > > select mbrid, discharge,=20 > > max(eligible_day_30) as eligible_day_30, > > max(eligible_day_29) as eligible_day_29, > > max(eligible_day_28) as eligible_day_28, > > max(eligible_day_27) as eligible_day_27, > > max(eligible_day_26) as eligible_day_26, > > max(eligible_day_25) as eligible_day_25, > > max(eligible_day_24) as eligible_day_24, > > max(eligible_day_23) as eligible_day_23, > > max(eligible_day_22) as eligible_day_22, > > max(eligible_day_21) as eligible_day_21, > > max(eligible_day_20) as eligible_day_20, > > max(eligible_day_19) as eligible_day_19, > > max(eligible_day_18) as eligible_day_18, > > max(eligible_day_17) as eligible_day_17, > > max(eligible_day_16) as eligible_day_16, > > max(eligible_day_15) as eligible_day_15, > > max(eligible_day_14) as eligible_day_14, > > max(eligible_day_13) as eligible_day_13, > > max(eligible_day_12) as eligible_day_12, > > max(eligible_day_11) as eligible_day_11, > > max(eligible_day_10) as eligible_day_10, > > max(eligible_day_09) as eligible_day_09, > > max(eligible_day_08) as eligible_day_08, > > max(eligible_day_07) as eligible_day_07, > > max(eligible_day_06) as eligible_day_06, > > max(eligible_day_05) as eligible_day_05, > > max(eligible_day_04) as eligible_day_04, > > max(eligible_day_03) as eligible_day_03, > > max(eligible_day_02) as eligible_day_02, > > max(eligible_day_01) as eligible_day_01, > > max(eligible_day_00) as eligible_day_00 > > from new2 > > group by mbrid, discharge=20 > > having > > (calculated eligible_day_30=3D1 and=20 > > calculated eligible_day_29=3D1 and > > calculated eligible_day_28=3D1 and > > calculated eligible_day_27=3D1 and > > calculated eligible_day_26=3D1 and > > calculated eligible_day_25=3D1 and > > calculated eligible_day_24=3D1 and > > calculated eligible_day_23=3D1 and > > calculated eligible_day_22=3D1 and > > calculated eligible_day_21=3D1 and > > calculated eligible_day_20=3D1 and > > calculated eligible_day_19=3D1 and > > calculated eligible_day_18=3D1 and > > calculated eligible_day_17=3D1 and > > calculated eligible_day_16=3D1 and > > calculated eligible_day_15=3D1 and > > calculated eligible_day_14=3D1 and > > calculated eligible_day_13=3D1 and > > calculated eligible_day_12=3D1 and > > calculated eligible_day_11=3D1 and > > calculated eligible_day_10=3D1 and > > calculated eligible_day_09=3D1 and=20 > > calculated eligible_day_08=3D1 and > > calculated eligible_day_07=3D1 and > > calculated eligible_day_06=3D1 and > > calculated eligible_day_05=3D1 and > > calculated eligible_day_04=3D1 and > > calculated eligible_day_03=3D1 and > > calculated eligible_day_02=3D1 and > > calculated eligible_day_01=3D1 and > > calculated eligible_day_00=3D1); > > quit; > > run; > > ----- Original Message -----=20 > From: Laptop...@GMAIL.COM=20 > To: SA...@LISTSERV.UGA.EDU=20 > Sent: Monday, May 19, 2008 1:43 PM > Subject: Re: Nested Data Accesses? > > Is there any way to modify my SQL query to use two tables in FROM with > the same column names? For example, I have the old id table and the > new one that I'm in the process of generating... I would like to use > the values that I generate in the calculation of future value > calculations. Is this possible? I tried putting FROM oldids, > unmatched but I got duplicate column error.

If I'm understanding that correctly (i.e. that a value calculated can be used later in the query), then that is not the solution I'm looking for. My problem is the following:

PROC SQL NOPRINT; UPDATE unmatched AS needid SET idfield2=(SELECT idfield2 FROM oldids(OBS=1) AS oldid WHERE needid.matchfield EQ oldid.matchfield GROUP BY idfield2 HAVING MAX(idfield3)+1 < 99), SET idfield3=<similar code here ending with max(idfield3)+1 being assigned>; QUIT;

After the first record of unmatched is taken into consideration, I want that value to be in the calculation of the next idfield2 and idfield3. As you will note, max(idfield3) is essentially being incremented for some idfield2 every time a record is processed. When this overflows the space allotted for it, I need to calculate the next idfield2.

Basically, my "FROM" needs modification to include both oldids AND the continually updating unmatched, but this is where I am having my problem. Thank you for your help though.