Haven't been following this, but it seems more like a simple set or union vs. a merge: ex: --but if Toby was in on this, then you got it right

data a; f1=1; ntss='A'; trial_1=5.13; output; f1=2; ntss='A'; trial_1=5.07; output; f1=3; ntss='A'; trial_1=6.22; output; f1=4; ntss='A'; trial_1=6.10; output; f1=5; ntss='A'; trial_1=5.96; output; f1=6; ntss='A'; trial_1=5.70; output; run;

data b; f1=1; ntss='B'; trial_2=5.17; output; f1=2; ntss='B'; trial_2=5.25; output; f1=3; ntss='B'; trial_2=6.32; output; f1=4; ntss='B'; trial_2=6.05; output; f1=5; ntss='B'; trial_2=5.93; output; f1=6; ntss='B'; trial_2=5.75; output; run;

proc sql feedback undo_policy=none; create table c as select a.f1 as id, a.ntss, a.trial_1 as trial from a UNION select b.f1 as id, b.ntss, b.trial_2 as trial from b order by 1,3; quit;

proc print data=c; run;

On 12/4/06, Howard Schreier <hs AT dc-sug DOT org> <nospam@howles.com> wrote: > > On Mon, 4 Dec 2006 09:23:39 -0800, procattrib <sharadatata@GMAIL.COM> > wrote: > > >Hi, > > > >I have a total of 12 observations in 2 columns which I need to merge > >into a single column based on id. The variables I have are id, trial_1 > >(which I tracked as A), trial_2 ( tracking variable B) which belong to > >a single process called NTSS. I am having trouble in merging the two > >data sets in proc sql after assigning the tracking variable. Could you > >please tell me where am I going wrong or suggest me what do I need to > >do. > > > >The code is: > >PROC SQL; > >CREATE TABLE T1 AS SELECT trial_1 as NTSS, f1 as ID, > >case when (Trial_1=NTSS) > >then 'A' end as Trial_1 > >>From WORK.QA2SQL(keep=TRIAL_1 f1); > >select * from t1; > >QUIT; > >PROC SQL; > >CREATE TABLE T2 AS SELECT trial_2 as NTSS,f1 as ID, > >case when (Trial_2=NTSS) > >then 'B' end as Trial_2 > >>From WORK.QA2SQL(keep=TRIAL_2 f1); > >select * from t2; > >QUIT; > >proc sql; > >create table TEMP as select A.NTSS, A.ID,A.Trial_1, B.Trial_2, B.NTSS > >from T1 A inner JOIN T2 B > >ON (A.ID=B.ID ); > >select * from TEMP; > >quit; > > > >Outputs when I run the above program code are: > >Trial 1 > >F1 Trial_1 > >5.13 1 A > >5.07 2 A > >6.22 3 A > >6.1 4 A > >5.96 5 A > >5.7 6 A > > > >Trial 2 > >F1 Trial_2 > >5.17 1 B > >5.25 2 B > >6.32 3 B > >6.05 4 B > >5.93 5 B > >5.75 6 B > > > >Trial 1 > >F1 Trial_1 Trial_2 > >5.13 1 A B > >5.07 2 A B > >6.22 3 A B > >6.1 4 A B > >5.96 5 A B > >5.7 6 A B > > > >The output that I want is : > >Obs F1 TRIAL NTSS > >1 1 A 5.13 > >2 1 B 5.17 > >3 2 A 5.07 > >4 2 B 5.25 > >5 3 A 6.22 > >6 3 B 6.32 > >7 4 B 6.05 > >8 4 A 6.10 > >9 5 B 5.93 > >10 5 A 5.96 > >11 6 A 5.70 > >12 6 B 5.75 > > > >Thanks in advance > >Sas learner > > First, you might have provided the data (QA2SQL). My best guess: > > data QA2SQL; > input TRIAL_1 TRIAL_2 f1; > cards; > 5.13 5.17 1 > 5.07 5.25 2 > 6.22 6.32 3 > 6.1 6.05 4 > 5.96 5.93 5 > 5.7 5.75 6 > ; > > There's no need to exit PROC SQL and re-start for each statement. > > Your CASE code does nothing since the condition compares a column with its > own clone; that's always going to test as true. > > It's advantageous to give the A/B column the same name in both tables > (TRIAL, with no suffixes). > > So try > > CREATE TABLE T1 AS SELECT trial_1 as NTSS, f1 as ID, 'A' as Trial > From WORK.QA2SQL(keep=TRIAL_1 f1); > > select * from t1; > > CREATE TABLE T2 AS SELECT trial_2 as NTSS, f1 as ID, 'B' as Trial > From WORK.QA2SQL(keep=TRIAL_2 f1); > > select * from t2; > > From there, a UNION (as Toby suggested) is the more natural way to > combine. > But it can be done with a JOIN. a lot of matching conditions and COALESCE > function calls are needed, unless you let NATURAL JOIN take care of all > that. Try: > > create table TEMP as select ID, Trial, NTSS format=5.2 > from T1 natural full JOIN T2 > order by ID, Trial; >