BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MeganE
Pyrite | Level 9

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.

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
MeganE
Pyrite | Level 9

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

12 REPLIES 12
ballardw
Super User

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.

MeganE
Pyrite | Level 9

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.

MeganE
Pyrite | Level 9

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.

Reeza
Super User

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

 

 

MeganE
Pyrite | Level 9

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

MeganE
Pyrite | Level 9

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;

Reeza
Super User

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;
Ksharp
Super User
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;
 


MeganE
Pyrite | Level 9

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

Ksharp
Super User
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 .
Tom
Super User Tom
Super User

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.

 

MeganE
Pyrite | Level 9

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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