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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.