DATA Step, Macro, Functions and more

Comparing a row in a dataset with the next row

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 128
Accepted Solution

Comparing a row in a dataset with the next row

Hi,

 

I have a table with two rows in it, and 18 variables.  The first row contains summary values from other stuff and the second row is my summary of the same values (it's a quality control check).  I want to make sure that my totals match the totals from the other source.

 

So, two rows, 18 columns.  Looks vaguely like this:

                 c4        c5        c6   etc....

all             100    293       284

confirm     100    291       284

 

And i want to add a row that compares the all value in C4 with the comfirm value for C4, etc.  So i end up with this:

                 c4        c5        c6   etc....

all             100    293       284

confirm     100    291       284

match        1        0            1

 

Thanks,
Megan

 

pw.  Once i get this to work, it'll be applied to about 56 different tables, so it's not that i just don't want to eye scan the values themselves.  There are going to be quite a few of them.

 

Thanks.

 

 

 

 


Accepted Solutions
Solution
‎06-12-2016 10:48 AM
Frequent Contributor
Posts: 128

Re: Comparing a row in a dataset with the next row

This works, but i'd like to leave the post open in case anyone knows of a function or something that can do it without the transposing.

 

/*select out the original all row and rename the variables*/
data d1_part1_1a_selectrows;
    set final.d1_final (drop=_break_ i);
    where source=1 and cat_order in (1 7) and grp_order in (1 2);
    /*rename*/
    %macro rename;
    %do i=4 %to 22;
        rename _c&i._=c&i.;
    %end;
    %mend;
    %rename;
    drop source;
run;

/*Transpose selectrows from wide to long*/
data d1_part1_2_transpose_all (drop=c: grp_order i);
    set d1_part1_1a_selectrows;
    array xvar(19) c4-c22;
    do i=1 to 19;
        where grp_char="All";
        var=vname(xvar(i));
        total=xvar(i);
        output;
    end;
run;


/*Get totals on your own to compare to the ALL row.*/
proc sql;
    create table d1_part1_3_confirm as
    select cat_order,
    %macro sum;
    %do i=4 %to 22;
            sum(C&i.) as C&i.,
    %end;
    %mend;
    %sum
    "confirm" as grp_char
    from d1_part1_1a_selectrows
    where cat_order=1
    group by cat_order;

    alter table d1_part1_3_confirm
    drop cat_order;
quit;

/*Transpose confirm from wide to long*/
data d1_part1_4_transpose_confirm (drop=c: i);
    set d1_part1_3_confirm;
    array xvar(19) c4-c22;
    do i=1 to 19;
        var=vname(xvar(i));
        total=xvar(i);
        output;
    end;
run;

/*Merge old with new sums*/
proc sql;
    create table D1_part1_5_compare as
    select coalescec(a.var, b.var) as var,
            a.total as orig_total,
            b.total as confirm_total,
            case when a.total=b.total then 1 else 0 end as matchflag
    from D1_part1_2_transpose_all a full join D1_part1_4_transpose_confirm b
    on upcase(a.var)=upcase(b.var);
quit;

View solution in original post


All Replies
Super User
Posts: 10,476

Re: Comparing a row in a dataset with the next row

I might suggest instead of mixing the two data sources (you don't say how you're doing this) that perhaps using PROC COMPARE may be better option.

Frequent Contributor
Posts: 128

Re: Comparing a row in a dataset with the next row

I suppose that could work, but if someone knew how to add another row so that i could have both totals all together with a match flag all in the same table, that'd be ideal.

Frequent Contributor
Posts: 128

Re: Comparing a row in a dataset with the next row

Actually, i just looked into and it's not really what i want. I don't want all the flags, i have to pull out everything but the total columns b/c they come back mismatched.

 

I'd really like a way to compare row one with row two and create a third row with a flag at the same time.

Super User
Posts: 17,775

Re: Comparing a row in a dataset with the next row

What about transposing data? It becomes trivial in the long form. Are all variables numeric?

 

 

Frequent Contributor
Posts: 128

Re: Comparing a row in a dataset with the next row

That's what i'm fiddling with now.  Yes, they are all numeric, and i just finished transposing both tables and am writing the compare code now.  Just wasn't sure it would work so i hadn't updated the post.  Smiley Happy

Solution
‎06-12-2016 10:48 AM
Frequent Contributor
Posts: 128

Re: Comparing a row in a dataset with the next row

This works, but i'd like to leave the post open in case anyone knows of a function or something that can do it without the transposing.

 

/*select out the original all row and rename the variables*/
data d1_part1_1a_selectrows;
    set final.d1_final (drop=_break_ i);
    where source=1 and cat_order in (1 7) and grp_order in (1 2);
    /*rename*/
    %macro rename;
    %do i=4 %to 22;
        rename _c&i._=c&i.;
    %end;
    %mend;
    %rename;
    drop source;
run;

/*Transpose selectrows from wide to long*/
data d1_part1_2_transpose_all (drop=c: grp_order i);
    set d1_part1_1a_selectrows;
    array xvar(19) c4-c22;
    do i=1 to 19;
        where grp_char="All";
        var=vname(xvar(i));
        total=xvar(i);
        output;
    end;
run;


/*Get totals on your own to compare to the ALL row.*/
proc sql;
    create table d1_part1_3_confirm as
    select cat_order,
    %macro sum;
    %do i=4 %to 22;
            sum(C&i.) as C&i.,
    %end;
    %mend;
    %sum
    "confirm" as grp_char
    from d1_part1_1a_selectrows
    where cat_order=1
    group by cat_order;

    alter table d1_part1_3_confirm
    drop cat_order;
quit;

/*Transpose confirm from wide to long*/
data d1_part1_4_transpose_confirm (drop=c: i);
    set d1_part1_3_confirm;
    array xvar(19) c4-c22;
    do i=1 to 19;
        var=vname(xvar(i));
        total=xvar(i);
        output;
    end;
run;

/*Merge old with new sums*/
proc sql;
    create table D1_part1_5_compare as
    select coalescec(a.var, b.var) as var,
            a.total as orig_total,
            b.total as confirm_total,
            case when a.total=b.total then 1 else 0 end as matchflag
    from D1_part1_2_transpose_all a full join D1_part1_4_transpose_confirm b
    on upcase(a.var)=upcase(b.var);
quit;

Super User
Posts: 17,775

Re: Comparing a row in a dataset with the next row

If you really wanted to keep it in wide format you could use arrays/retain/end. 

 

Untested:

 

Data want;
Set have end=eof;

Array c(*) c1-c20;
Array c_lag(*) clag1-clag20;

Retain clag1-clag20;

If _n_=1 then do;
Do i=1 to dim(c);
C_lag(I) = c(I);
End;

Output;

If eof then do;
Do I=1 to 20;
C(I)=round(c(I)-c_lag(I), 0.1);
End;
Output;
End;
Run;
Super User
Posts: 9,671

Re: Comparing a row in a dataset with the next row

It would be very easy for IML . Do you want IML code ?
The following is SQL code .


data have;
input c4        c5        c6 ;
cards;  
100    293       284
100    291       284
;
run;
proc transpose data=have(obs=0) out=temp;
 var _all_;
run;
proc sql noprint;
 select catx(' ','range(',_name_,')=0 as ',_name_) into : list separated by ','
  from temp;
 create table matched as
  select &list from have;
quit;
data want;
 set have matched;
run;
 


Frequent Contributor
Posts: 128

Re: Comparing a row in a dataset with the next row

I don't know what IML code is. 

 

And i'm unfamiliar with the range function.  I've looked it up now, but the SAS page doesn't have =0 on it.  What's that part?  I've run what you sent and i see that it's working, but i'm not clear on how selecting "range( c4 )=0 as c4,range( c5 )=0 as c5,range( c6 )=0 as c6" from have gives me the 1/0s that i'm looking at, lol.  How did that work??  Can you please explain the range()=0 function a little more? 

 

Thanks

Super User
Posts: 9,671

Re: Comparing a row in a dataset with the next row

In SQL range() compare the min value and max value in a variable/column. For Example: range( c4 )=0 first compare the min value and max value of C4 (e.g. |max-min| ) , if it =0 ,C4 should have all the same value. range( c4 )=0 is a condition statement in SQL , if true ,it will return 1 ,otherwise return 0 .
Super User
Super User
Posts: 6,498

Re: Comparing a row in a dataset with the next row

Use PROC COMPARE.  

proc compare out=want outall
  data=have(obs=1)
  compare=have(firstobs=2)
;
run;

You might want to create a macro to do it if you need to run it for 56 separate data set.

 

Frequent Contributor
Posts: 128

Re: Comparing a row in a dataset with the next row

Thanks, but proc compare isn't what i want.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 547 views
  • 0 likes
  • 5 in conversation