Compare Columns by Different Row Loactions

NumberMuncher — Wed, 27 Apr 2022 20:44:14 GMT

Hello all,

I've seen similar questions but none seem to be what I am looking for.

I have one dataset with 2 variables I'd like to compare by ID. I can use proc compare but the rows start at different dynamic locations. I can't figure out how to either move my compare variable (x2) up to the correct row location or how to lag it behind to start at a dynamic location. Each id has a different number of groups.

This is the data I have:

id	group	x1	x2
1	1	4/16/2021
1	2	4/19/2021
1	3	4/28/2021
1	4	6/4/2021
1	1		4/16/2020
1	2		4/19/2021
1	3		4/28/2021
1	4		6/4/2021
2	1	8/12/2020
2	2	9/16/2020
2	3	10/8/2020
2	4	2/8/2021
2	5	9/29/2021
2	1		8/12/2020
2	2		9/16/2021
2	3		10/8/2020
2	4		2/8/2021
2	5		9/29/2021

And this is what I want (or at least a way to start the proc compare to lag by the max range in each id's group number):

id	group	x1	x2	x3	x4	x5
1	1	4/16/2021		4/16/2021	4/16/2020	0
1	2	4/19/2021		4/19/2021	4/19/2021	1
1	3	4/28/2021		4/28/2021	4/28/2021	1
1	4	6/4/2021		6/4/2021	6/4/2021	1
1	1		4/16/2020
1	2		4/19/2021
1	3		4/28/2021
1	4		6/4/2021
2	1	8/12/2020		8/12/2020	8/12/2020	1
2	2	9/16/2020		9/16/2020	9/16/2021	0
2	3	10/8/2020		10/8/2020	10/8/2020	1
2	4	2/8/2021		2/8/2021	2/8/2021	1
2	5	9/29/2021		9/29/2021	9/29/2021	1
2	1		8/12/2020
2	2		9/16/2021
2	3		10/8/2020
2	4		2/8/2021
2	5		9/29/2021

Any help in the right direction would be awesome. Please and thank you!

Re: Compare Columns by Different Row Loactions

PaigeMiller — Wed, 27 Apr 2022 21:27:49 GMT

UNTESTED CODE

data want;
    merge have(keep=id group x1 where=(not missing(x1))) 
        have(keep=id group x2 where=(not missing(x2)));
    by id group;
run;

This code eliminates the redundant columns in your requested data set. This code makes no attempt to compute x5 as you did not provide an explanation of what it is.

If you want tested code, please provide data as SAS data step code, which you can type in yourself, or use these instructions. Do not provide data in any other format.

Re: Compare Columns by Different Row Loactions

NumberMuncher — Thu, 28 Apr 2022 17:55:32 GMT

Thanks! This was very helpful.

topic Re: Compare Columns by Different Row Loactions in SAS Programming

Compare Columns by Different Row Loactions

Re: Compare Columns by Different Row Loactions

Re: Compare Columns by Different Row Loactions