Need to pull extra info (name) through as well as ID number

JBOrlov — Thu, 14 Nov 2013 15:03:44 GMT

Hi all,

I have the following code which produces the exception report I need for A B C, split across 3 different Excel tabs.

What I'm wanting to pull through too is the NAME as well as the ID. The name is on all the feeder info, I just simply need to pull it through.

Any ideas? I'n not really wanting to change the code much, just as little as I need to.

Many thanks,

proc sort data=DATA_A;
by ID;
run;

proc sort data=DATA_B;
by ID;
run;

PROC SQL;
CREATE INDEX ID ON DATA_A(ID);
CREATE INDEX ID ON DATA_B(ID);
QUIT;

PROC SQL;
CREATE TABLE DATA_DIFS AS
SELECT DATA_A.ID,
'VAR_IN_A' AS VAR_FIELD,
DATA_A.A as DATA_A_A,
DATA_A.B as DATA_A_B,
DATA_A.C as DATA_A_C,
DATA_B.A as DATA_B_A,
DATA_B.B as DATA_B_B,
DATA_B.C as DATA_B_C
FROM DATA_A AS DATA_A,
DATA_B AS DATA_B
WHERE DATA_A.ID = DATA_B.ID
AND DATA_A.A NE DATA_B.A
UNION ALL
SELECT DATA_A.ID,
'VAR_IN_B' AS VAR_FIELD,
DATA_A.A as DATA_A_A,
DATA_A.B as DATA_A_B,
DATA_A.C as DATA_A_C,
DATA_B.A as DATA_B_A,
DATA_B.B as DATA_B_B,
DATA_B.C as DATA_B_C
FROM DATA_A AS DATA_A,
DATA_B AS DATA_B
WHERE DATA_A.ID = DATA_B.ID
AND DATA_A.B NE DATA_B.B
UNION ALL
SELECT DATA_A.ID,
'VAR_IN_C' AS VAR_FIELD,
DATA_A.A as DATA_A_A,
DATA_A.B as DATA_A_B,
DATA_A.C as DATA_A_C,
DATA_B.A as DATA_B_A,
DATA_B.B as DATA_B_B,
DATA_B.C as DATA_B_C
FROM DATA_A AS DATA_A,
DATA_B AS DATA_B
WHERE DATA_A.ID = DATA_B.ID
AND DATA_A.C NE DATA_B.C;
QUIT;

Re: Need to pull extra info (name) through as well as ID number

JohnW_ — Thu, 14 Nov 2013 16:10:58 GMT

Either I don't understand the problem or you are uncomfortable with SQL.

Assuming the latter, and that name and ID match on each table (otherwise join on NAME as well):

PROC SQL;
CREATE TABLE DATA_DIFS AS
SELECT DATA_A.ID,
DATA_A.NAME,
'VAR_IN_A' AS VAR_FIELD,
DATA_A.A as DATA_A_A,
DATA_A.B as DATA_A_B,
DATA_A.C as DATA_A_C,
DATA_B.A as DATA_B_A,
DATA_B.B as DATA_B_B,
DATA_B.C as DATA_B_C
FROM DATA_A AS DATA_A,
DATA_B AS DATA_B
WHERE DATA_A.ID = DATA_B.ID
AND DATA_A.A NE DATA_B.A
UNION ALL
SELECT DATA_A.ID,
DATA_A.NAME,
'VAR_IN_B' AS VAR_FIELD,
DATA_A.A as DATA_A_A,
DATA_A.B as DATA_A_B,
DATA_A.C as DATA_A_C,
DATA_B.A as DATA_B_A,
DATA_B.B as DATA_B_B,
DATA_B.C as DATA_B_C
FROM DATA_A AS DATA_A,
DATA_B AS DATA_B
WHERE DATA_A.ID = DATA_B.ID
AND DATA_A.B NE DATA_B.B
UNION ALL
SELECT DATA_A.ID,
DATA_A.NAME,
'VAR_IN_C' AS VAR_FIELD,
DATA_A.A as DATA_A_A,
DATA_A.B as DATA_A_B,
DATA_A.C as DATA_A_C,
DATA_B.A as DATA_B_A,
DATA_B.B as DATA_B_B,
DATA_B.C as DATA_B_C
FROM DATA_A AS DATA_A,
DATA_B AS DATA_B
WHERE DATA_A.ID = DATA_B.ID
AND DATA_A.C NE DATA_B.C;
QUIT;

topic Need to pull extra info (name) through as well as ID number in SAS Procedures

Need to pull extra info (name) through as well as ID number

Re: Need to pull extra info (name) through as well as ID number