topic Re: Comparing a row in a dataset with the next row in SAS Programming

Comparing a row in a dataset with the next row

MeganE — Fri, 03 Jun 2016 17:33:34 GMT

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.

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

ballardw — Fri, 03 Jun 2016 18:06:43 GMT

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.

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

MeganE — Fri, 03 Jun 2016 18:15:43 GMT

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.

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

MeganE — Fri, 03 Jun 2016 18:23:44 GMT

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.

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

Reeza — Fri, 03 Jun 2016 19:12:25 GMT

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

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

MeganE — Fri, 03 Jun 2016 19:13:46 GMT

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. 🙂

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

MeganE — Fri, 03 Jun 2016 19:18:16 GMT

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;

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

Reeza — Fri, 03 Jun 2016 19:22:38 GMT

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;

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

Ksharp — Sat, 04 Jun 2016 04:10:28 GMT

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;

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

Tom — Sat, 04 Jun 2016 05:38:34 GMT

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.

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

MeganE — Mon, 06 Jun 2016 11:59:01 GMT

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

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

MeganE — Mon, 06 Jun 2016 12:07:35 GMT

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

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

Ksharp — Tue, 07 Jun 2016 01:43:11 GMT

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 .