Comparing tables

[Ranjeeta]

Hello,

I have tables for 6 years 2012 to 2017

I have a list of codes in each table that I want to compare 

Basically I want to create a table that tells me which codes have changed over the years?

E.g.  if 1HA80QB is present from 2012 to 2016 and then it disappeared in 2017 i need to kow that

or a new code appeared in 2017 onwards which was not present in prior years?

How would i compare these 6 tables? 

Please advise

Thanks

[Reeza · Accepted solution]

Do the tables have duplicates or is each code only in there once? The simplest method is to stack the data sets, add a variable for year (use INDSNAME) and then transpose the data to a wide format. You'll get a data set that looks like:

CODE Year2017 Year2016 .. Year2012 with a 1 or . for each code that's missing.

[Ranjeeta]

thankyou I stacked and did a proc freq
Is it correct to use 2 where statements in a proc freq
where section=1;
pxblock in (1,2,);
run;

[Reeza]

Not in the way shown. Instead combine them using an AND or OR as appropriate.

where section = 1 AND pxblock in (1,2);

[Reeza]

Note that proc freq will give you the output displayed. If you want it in a SAS data set, you'll need the transpose step.

[Ranjeeta]

How would I write the proc transpose
proc transpose data=combined (keep =fyear ccicode);
var fyear;
run;

[Reeza]

---

@Ranjeeta wrote:
How would I write the proc transpose
proc transpose data=combined (keep =fyear ccicode);
var fyear;
run;

---

most likely like this:

proc transpose data=have out=want prefix=Year;
by Code;
ID year;
Var ; *what value you want to be transposed, may need to add one in previous step;
run;

[Ranjeeta]

data summary other
merge px2011(in=In2011) px 2012(In=In2012);
by px_code;
if in (In2011,In2012) then output summary
else output other;
run;

Can you also please advise how to modify the above step to create a summary column if the code is present in the 2 years
Basically want to see a table of codes that would not have a total of 2
Im doing the above for 7 years actuaaly
Thnx

[Reeza]

I meant append, not merge.

Untested obviously, but this is the idea.

data long;
set px2011-px2017 indsname = source;

name = source;
DummyVar=1;
run;

proc sort data=have; 
by px_code name;
run;

proc transpose data=long out=wide prefix=Year_;
by px_code;
id name;
var DummyVar;
run;

data want;
set wide;

CountYears = sum(of year_:);
run;

[Ranjeeta]

sas just hangs when i try to do proc transpose