LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (August 2007, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Thu, 23 Aug 2007 08:47:28 -0400
Reply-To:     Richard Reeves <reeves@STUDENTCLEARINGHOUSE.ORG>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Richard Reeves <reeves@STUDENTCLEARINGHOUSE.ORG>
Subject:      Re: which to use, Proc SQL or Proc Compare
Content-Type: text/plain; charset="US-ASCII"

Hi Johanna, I don't know how big your data are (rows) but if processing isn't an issue you could merge the base and week tables on id and another variable and create an indicator if the field changed and run this 20 times. In the example below you would merge on id and hours if a match exists then 1 otherwise 0 then merge on id and code if a match exists 1 otherwise 0. You can create a table of id with the indicators and use that for change analysis. If you are in a controlled environment work with the buffersize and _method commands to understand how the merge is working.

I can't really think of a better solution but there are better people on this list than me. But if you have less than 1 million records it probably is a reasonable option.

Rich

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@listserv.vt.edu] On Behalf Of Johanna Sent: Wednesday, August 22, 2007 3:17 PM To: SAS-L@LISTSERV.VT.EDU Subject: which to use, Proc SQL or Proc Compare

below is a small sample of a process that runs a comparison to detect changes to some data, every week.

the question I had was... which is better to track changes, Proc Sql or Proc Compare, when you consider that I'd like to know what part of the data changed

below, Mary Smith's hours changed and Bob Murdoch is a new person but Proc Compare won't tell me why the person is listed, just that something changed

in the real world, I have a dataset with about 20 variables and any one of them could change week over week, without having to analyze each separately, is there an elegant way to detect "What's new this week?"

the only condition I don't need to track is if the person does not exist the next week, ie. dropped, but everything else I would need to be aware of, ie. "Mary Smith's HOURS changed" and "Bob Murdoch is a NEW item", also the code could have changed but not in my sample below.

data base;input id hours code $3. name $20.; cards; 001 40 ABB Tom Jones 002 20 DBF Mary Smith ; run; data week;input id hours code $3. name $20.; cards; 001 40 ABB Tom Jones 002 40 DBF Mary Smith 003 40 ABB Bob Murdoch ; run; proc compare base=base compare=week out=result(where=(_type_="COMPARE")) outnoequal outbase outcomp outdif noprint;id id; run; data _null_;set ;put(_all_)(=);run;


Back to: Top of message | Previous page | Main SAS-L page