|
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;
| 
