topic Re: Finding Changes in Data in SAS Programming

Finding Changes in Data

BCNAV — Thu, 19 Oct 2017 14:53:45 GMT

I have a program that provides output as follows:

Year Month depart dest Count

2017 8 CYVR CYYZ 578

2017 8 CYOW CYYZ 578

These are airport departure and destination codes. I can select which month/year combo I want and all is good.

Here is the question, how can I compare two months (say August 2016 and August 2016) and have ONLY CHANGED CITY PAIRS come out.

I'm thinking I will need to concatenate depart and dest to, say, departdest (e.g., CYVRCYYZ from above) and use that variable to compare. Essentially I am only interesting in saying:

"When comparing August 2017 to August 2016 the following city pairs were added. When comparing August 2017 to August 2016 the following city pairs were removed."

Any initial ideas would be great

thx

Re: Finding Changes in Data

RW9 — Thu, 19 Oct 2017 15:00:24 GMT

You may be able to get away with:

proc sql;
  create table WANT as
  select  *
  from    (select distinct YEAR,MONTH,DEPART,DEST from HAVE)
  where   count(*) > 1;
quit;

Not not tested, post test data in the form of a datastep.

Re: Finding Changes in Data

Reeza — Thu, 19 Oct 2017 15:02:45 GMT

PROC SQL with INTERSECT for the same and EXCEPT for the differences. Or PROC COMPARE but I don't find that as easy to use. If you want more help, please post sample data and expected output.

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Re: Finding Changes in Data

BCNAV — Thu, 19 Oct 2017 15:24:17 GMT

Here is more info

I have two tables:

AUGUST_2017

AUGUST_2016

Each table contains airport city pair data (I can post data if needed). Data looks like:

AUGUST_2017:

Year   Month   depart   dest   Count
2017   8          CYVR   CYYZ      578
2017   8          CYYZ    CYVR     577
2017   8          CYOW CYYZ      530
2017   8          CYYZ    CYOW    529

AUGUST_2016:

Year   Month   depart   dest   Count
2016   8          CYVR   CYYZ      578
2016   8          CYYZ    CYVR     577
2016   8          CYOW CYYZ      530
2016   8          CYYZ    CYOW    529

and so on for thousands of flights.

I need to be able to query both existing tables.

The first query would be to check what is in AUGUST_2017 that is not in AUGUST_2016. These would be added flights.

The second query would be to check what is in AUGUST_2016 that is not in AUGUST_2017. These would be flights removed by the airline.

I don't think my original post was clear enough. Sorry.

Re: Finding Changes in Data

Reeza — Thu, 19 Oct 2017 15:35:03 GMT

Did EXCEPT not work? If not, post your code and log.

Re: Finding Changes in Data

BCNAV — Thu, 19 Oct 2017 15:51:00 GMT

I am not sure of the code..can you post the sql

PROC SQL;

CREATE TABLE EGTASK.TEST2 AS

SELECT t1.depart,

t1.dest

intersect EGTASK.AUGUST_2017 t1, EGTASK.AUGUST_2016 t2;

QUIT;

Re: Finding Changes in Data

Reeza — Thu, 19 Oct 2017 15:55:29 GMT

1. Your sample data all are the same so there are no differences to be found

2. You didn't put them as a data step so I'd have to write code to import your data first

3. To demonstrate an actual example I'd have to add new data that would show the differences

4. The code below is INTERSECT which would show the same in both data sets. EXCEPT will show the exceptions.

So even if the code worked, there's no way to test it at present with your sample data.

Here's a reference for the SQL operators with examples

http://www2.sas.com/proceedings/sugi31/242-31.pdf

Re: Finding Changes in Data

Reeza — Thu, 19 Oct 2017 17:07:11 GMT

Here's a solution that doesn't scale well to multiple data sets IMO. You need to manually code it and customize for each month.

And an SQL option that does scale, using EXCEPT

Re: Finding Changes in Data

BCNAV — Fri, 20 Oct 2017 12:23:50 GMT

@Reeza

Thank you!!!

I have tested and your solution is perfect. I ended up with:

/* The code below will get all flights that were removed from the previous year */
PROC SQL;
	CREATE TABLE EGTASK.ACM_FLT_REMOVED AS
		SELECT depart, dest
			FROM EGTASK.CITY_PR_TOTAL_PYEAR
				EXCEPT
			SELECT depart, dest from EGTASK.CITY_PR_TOTAL_CYEAR;
QUIT;

/* The code below will get all flights that were added in the current year */
PROC SQL;
	CREATE TABLE EGTASK.ACM_FLT_ADDED AS
		SELECT depart, dest
			FROM EGTASK.CITY_PR_TOTAL_CYEAR
				EXCEPT
			SELECT depart, dest from EGTASK.CITY_PR_TOTAL_PYEAR;
QUIT;

As you can see I have generalized it to current year (CYEAR) and previous year (PYEAR).

Thanks!