topic Re: Comparing tables in SAS Programming

Comparing tables

Ranjeeta — Wed, 05 Dec 2018 18:05:43 GMT

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

Re: Comparing tables

Reeza — Wed, 05 Dec 2018 18:09:21 GMT

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.

Re: Comparing tables

Ranjeeta — Wed, 05 Dec 2018 19:41:44 GMT

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;

Re: Comparing tables

Reeza — Wed, 05 Dec 2018 19:51:52 GMT

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

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

Re: Comparing tables

Reeza — Wed, 05 Dec 2018 19:52:45 GMT

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.

Re: Comparing tables

Ranjeeta — Wed, 05 Dec 2018 20:16:39 GMT

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

Re: Comparing tables

Ranjeeta — Wed, 05 Dec 2018 20:17:21 GMT

sas just hangs when i try to do proc transpose

Re: Comparing tables

Reeza — Wed, 05 Dec 2018 20:28:29 GMT

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

Re: Comparing tables

Ranjeeta — Wed, 05 Dec 2018 22:21:30 GMT

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

Re: Comparing tables

Reeza — Wed, 05 Dec 2018 22:47:59 GMT

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;