Comparing Month Over Month Datasets

[novinosrin]

@wheddingsjr  Thank you Sir/Madam, I am relieved more than glad. I felt emotionally bad that this didn't work out. I just don't give up easily. Anyways, I have a reason to have a pint tonight when I get home. 🙂

Btw, if you don't mind, may i request you to mark the solution that you deemed right as accepted and close the thread? Please and Thanks!

[wheddingsjr]

I certainly will. I was writing something and was going to do it afterwards.

Enjoy your pint. I will as well..LOL

[wheddingsjr]

Novinosrin

I think you are on to something. The OLD table I am using is an excel import. Could that be why the formats are coming up as different?

[Reeza]

What about a straight merge? You'll have to recreate your sort after.

data old;
input MEMNO 	COUNT	UNIQDOS	BEGDATE :mmddyy10.	COMBO ;
cards;
12345	1	1	01/03/18	1234543103
12345	.	0	01/03/18	1234543103
12345	2	1	01/05/18	1234543105
12345	3	1	01/07/18	1234543107
12345	4	1	01/09/18	1234543109
12345	5	1	01/10/18	1234543110
12345	.	0	01/10/18	1234543110
12345	6	1	01/12/18	1234543112
12345	7	1	01/13/18	1234543113
12345	8	1	01/14/18	1234543114
12345	9	1	01/16/18	1234543116
12345	10	1	01/17/18	1234543117
12345	11	1	01/19/18	1234543119
12345	.	0	01/19/18	1234543119
12345	12	1	01/21/18	1234543121
12345	13	1	01/23/18	1234543123
12345	14	1	01/24/18	1234543124
12345	15	1	01/26/18	1234543126
12345	16	1	01/27/18	1234543127
12345	17	1	01/28/18	1234543128
12345	18	1	01/30/18	1234543130
12345	19	1	01/31/18	1234543131
12345	20	1	02/02/18	1234543133
12345	.	0	02/02/18	1234543133
12345	21	1	02/04/18	1234543135
;


data new;
input MEMNO	RETRACT $	COUNT	UNIQDOS	BEGDATE  :mmddyy10.	COMBO;
drop retract;
cards;
12345	Y	1	1	01/03/18	1234543103
12345	Y	.	0	01/03/18	1234543103
12345	Y	2	1	01/05/18	1234543105
12345	Y	3	1	01/07/18	1234543107
12345	Y	4	1	01/09/18	1234543109
12345	Y	5	1	01/10/18	1234543110
12345	Y	.	0	01/10/18	1234543110
12345	Y	6	1	01/12/18	1234543112
12345	Y	7	1	01/13/18	1234543113
12345	Y	8	1	01/14/18	1234543114
12345	Y	9	1	01/16/18	1234543116
12345	Y	10	1	01/17/18	1234543117
12345	Y	11	1	01/19/18	1234543119
12345	Y	.	0	01/19/18	1234543119
12345	Y	12	1	01/21/18	1234543121
12345	Y	13	1	01/23/18	1234543123
12345	Y	14	1	01/24/18	1234543124
12345	Y	15	1	01/26/18	1234543126
12345	Y	16	1	01/27/18	1234543127
12345	Y	17	1	01/28/18	1234543128
12345	Y	18	1	01/30/18	1234543130
12345	Y	19	1	01/31/18	1234543131
12345	Y	20	1	02/02/18	1234543133
12345	Y	.	0	02/02/18	1234543133
12345	Y	21	1	02/04/18	1234543135
12345	N	22	1	02/06/18	1234543137
12345	N	23	1	02/07/18	1234543138
12345	N	24	1	02/09/18	1234543140
12345	N	.	0	02/09/18	1234543140
12345	N	25	1	02/11/18	1234543142
12345	N	26	1	02/13/18	1234543144
12345	N	27	1	02/16/18	1234543147
12345	N	.	0	02/16/18	1234543147
12345	N	28	1	02/18/18	1234543149
12345	N	29	1	02/21/18	1234543152
12345	N	.	0	02/21/18	1234543152
;



proc sort data=old; by memno count uniqdos begdate combo;
proc sort data=new; by memno count uniqdos begdate combo;run;

data want;
merge old (in=a) new(in=b);
by memno count uniqdos begdate combo;

if a and b then previousMonth='Y';
else if b then  previousMonth='N';
run;

[wheddingsjr]

Hi Reeza

It combined both tables and just added the N but not the Y

[Reeza]

---

@wheddingsjr wrote:

Hi Reeza

It combined both tables and just added the N but not the Y

---

No it didn't, there are Y and N in the files.

Run the exact code as posted and you get Y and N.

If it's not matching your data, then that means your data does not match the demo data. You can run proc contents on the data sets and compare the information - variable, types and formats to make sure the match. 

[wheddingsjr]

Reeza

I ran the report, it did add the Y and N but it most certainly DID combine the two tables as my results. You were a great deal of help with me at the beginning of this endeavor and I truly appreciate the assistance. However, I have to say, though it may not be your intent, it seems to me that you lose your patience easily. Perhaps it is because of my inexperience with SAS and my not fully understanding as quickly as YOU think I should. Whatever it is, In this total thread you have spoken to me more than once in a manner that was not only unappreciated but not necessary. I am not as learned as you are when it comes to this and I appreciate ALL the help the good people in this community provide, but I, nor anyone else should have to accept disrespect from you or anyone else here.

[Reeza]

I'm not reading 9 pages to figure out your logic.

Are you still inserting the record if it's new or are you just checking to see if it's new? If its the latter, change your process a little to make this easier, add a variable called monthProcessed to the data sets. Then you can sort and if there are duplicates, which you can check using First. and Last. (from previous question) to identify if a record is new this month and was present last month as well. 

Here are instructions on how to get your data into the forum as a data step so you can get tested solutions back:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

[wheddingsjr]

Thanks Reeza