Help using Base SAS procedures

How to output Values Comparison Summary in to a dataset?

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

How to output Values Comparison Summary in to a dataset?

Greetings,

I hope you could help me as you have always helped me.  I am trying to create a dataset based on the summary report generated by Proc Compare. 

I was able to find some information regarding OUTSTAT=, but that is not exactly what I want.

I am looking to create a data set based on the summary generated for unequal values as shown in the screenshot below.

Does anybody have any suggestions?

2012-02-29_SAS Enterprise Guide - RHI Provider Parallel Project.egp.png

Thank you kindly.

Cheers,

Yeti


Accepted Solutions
Solution
‎02-29-2012 08:31 PM
Respected Advisor
Posts: 3,124

Re: How to output Values Comparison Summary in to a dataset?

Slick approach. However, there is the catch: monotonic() is undocumented ,unsupported by SAS. Which means, there is a possibility (although remote) that you wake up one day and your code stop working and you can't complain about it.

If you are sure your 'sum' will always end up the way it is, here is another approach, which is officially supported by SAS without involving macro variables or SQL, and I also suspect it will be more efficient as well.

data h1 h2;

set sashelp.class;

output h1;

age=age*10;

weight=weight*5;

height=height*5;

output h2;

run;

ods listing close;

ods output comparesummary=sum;

proc compare base=h1 compare=h2 ;

run;

ods output close;

ods listing;

data want (drop=i type batch _s);

array vr(*) $ variable type len ndif maxdif;

do _n_=1 by 1 until (last);

set sum end=last nobs=nobs;

if upcase(compress(batch))=    'VARIABLESWITHUNEQUALVALUES' THEN _s=_n_;

end;

do _n_=_s+4 to nobs-1;

   set sum point=_n_;

   do i=1 to dim(vr);

      vr(i)=scan(strip(batch),i);

end;

output;

end;

stop;

run;

Good Luck!

Haikuo

Edit: Yeti, I hesitated on offering critique on your code, but considering it may help you in the future, here is my 2 cents, hope you don't mind:

1. You don't need to create table 'sum1' in order to assign values to marcro variables. Creating unnecessary table not only slow you down , but also take more storage. Simply comment out 'create table' statement, the rest of your code will still work.

2. Why drop all of the work library tables? they will be dropped automatically once the session ends. Unless you really are strained by runtime storage shortage, and then you will probably want  considering using views instead of tables.

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

Re: How to output Values Comparison Summary in to a dataset?

I don't see any easy way to get exactly what you want. To get you started, One option that can get all of the elements you need would be ODS output;

data h1 h2;

set sashelp.class;

output h1;

age=age*10;

weight=weight*5;

height=height*5;

output h2;

run;

ods listing close;

ods output comparesummary=sum;

proc compare base=h1 compare=h2;

run;

ods output close;

ods listing;

You will find all of the elements around the last a few records in table 'sum',  while they are concatenated together, so you will need some function help to extract them out (scan() etc.)

Good luck,

Haikuo

Respected Advisor
Posts: 3,124

Re: How to output Values Comparison Summary in to a dataset?

For the example I have addressed, a post-ODS approach could be:

data want (drop=i type batch);

array vr(*) $ variable type len ndif maxdif;

do _n_=34 to 36;

   set sum point=_n_;

   do i=1 to dim(vr);

      vr(i)=scan(strip(batch),i);

end;

output;

end;

stop;

run;

Please note, I have made them all 'character' to simplify the procedure. You need to decide the index for _n_ each time by checking the 'sum'. Although you could use some automation that  locates the data chunk that you want, for the peace of mind, I would still check it for sure.

Regards,

Haikuo

Edit: It appears to me that even the array elements are subjected to change as well ( you could have len1, len2, etc.), so you will have to open and check 'sum' anyway.

Contributor
Posts: 32

Re: How to output Values Comparison Summary in to a dataset?

Thank you very much Haikuo.

I have to admit that this one is little advance for me, but I am eager to learn.  I think it is almost there.

The Sum dataset appears to have:

2012-02-29_SAS Enterprise Guide.png

The WANT dataset contains:

2012-02-29_SAS Enterprise Guide.png

I am at a loss how WANT dataset was derived from SUM dataset.

I will take a look at this in more details later and get back to you.

This is really out of my leage. :smileycry:

Once again, thank you very much for your help.

Cheers,

Yeti

Contributor
Posts: 32

Re: How to output Values Comparison Summary in to a dataset?

Ha, now I understand it more.  I just had to increase the column width for 'Batch'. :smileylaugh:

Very neat.

I will have to play more with it.

Thank you very much again.

Yeti

Contributor
Posts: 32

Re: How to output Values Comparison Summary in to a dataset?

What I did was to look for the string: Variables with Unequal Values

I believe this will always be one entry in the SUM table.

Bases on that, I managed to insert the values to the array.

Here is my final code:

data h1 h2;
set sashelp.class;
output h1;
age=age*10;
weight=weight*5;
height=height*5;
output h2;
run;

ods listing close;
ods output comparesummary=sum;

proc compare base=h1 compare=h2 ;
run;

ods output close;
ods listing;

proc sql noprint ;
create table sum1 as
select monotonic() as row, trim(left((batch))) as batch from sum;

select row into :start_row from sum1 where batch = 'Variables with Unequal Values';
select max(row) into :end_row from sum1;

quit;

data want (drop=i type batch);
array vr(*) $ variable type len ndif maxdif;
do _n_=&start_row+4 to &end_row-1;
   set sum point=_n_;
   do i=1 to dim(vr);
      vr(i)=scan(strip(batch),i);
end;
output;
end;
stop;
run;

proc sql ;
drop table sum;
drop table sum1;
drop table h1;
drop table h2;
quit;

Once again thank you very much.  I am very grateful.

cheers,

Yeti

Solution
‎02-29-2012 08:31 PM
Respected Advisor
Posts: 3,124

Re: How to output Values Comparison Summary in to a dataset?

Slick approach. However, there is the catch: monotonic() is undocumented ,unsupported by SAS. Which means, there is a possibility (although remote) that you wake up one day and your code stop working and you can't complain about it.

If you are sure your 'sum' will always end up the way it is, here is another approach, which is officially supported by SAS without involving macro variables or SQL, and I also suspect it will be more efficient as well.

data h1 h2;

set sashelp.class;

output h1;

age=age*10;

weight=weight*5;

height=height*5;

output h2;

run;

ods listing close;

ods output comparesummary=sum;

proc compare base=h1 compare=h2 ;

run;

ods output close;

ods listing;

data want (drop=i type batch _s);

array vr(*) $ variable type len ndif maxdif;

do _n_=1 by 1 until (last);

set sum end=last nobs=nobs;

if upcase(compress(batch))=    'VARIABLESWITHUNEQUALVALUES' THEN _s=_n_;

end;

do _n_=_s+4 to nobs-1;

   set sum point=_n_;

   do i=1 to dim(vr);

      vr(i)=scan(strip(batch),i);

end;

output;

end;

stop;

run;

Good Luck!

Haikuo

Edit: Yeti, I hesitated on offering critique on your code, but considering it may help you in the future, here is my 2 cents, hope you don't mind:

1. You don't need to create table 'sum1' in order to assign values to marcro variables. Creating unnecessary table not only slow you down , but also take more storage. Simply comment out 'create table' statement, the rest of your code will still work.

2. Why drop all of the work library tables? they will be dropped automatically once the session ends. Unless you really are strained by runtime storage shortage, and then you will probably want  considering using views instead of tables.

Contributor
Posts: 32

Re: How to output Values Comparison Summary in to a dataset?

Sweet, thank you very much Haikuo.  I did not know about the monotonic() not being standard in SAS.  You are amazing!

Looks like we need to take another approach in reading the data.  Notice my first screenshot:

Some Len2 and Ndif values are not separated.  This is obviously SAS limitation, I can easily use substr() function to separate those without any problem.  I do not really need the MaxDif and MissDif for my report.  So I am good.

Regarding your suggestions, no I don't mind at all.    I am not a hot shot guy when it comes to SAS, so I am always learning.

1: Assigning values to macro variables are in separate SELECT statements, thus no table creation. :smileysilly:

2: I agree, I usually save them in separate LIBRARY not in WORK library.  It is just a habbit I acquired over the time.  Another thing is that my SAS EG is always on in my VMWare, another bad habbit. Smiley Wink

Once again thank you very much for all your help.

Cheers,

Yeti

EDIT: 1: I think I now understand your 1st comment.  I had to create SUM1 to get the value ROW (using monotonic), which I do not need now after learning from your code.  I must create tables because SAS can not output my results as my tables are usually very large.

Contributor
Posts: 41

Re: How to output Values Comparison Summary in to a dataset?

Hey yeti,

how were you able to separate len2 from ndiff? How did you find out the length of len2?

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 787 views
  • 0 likes
  • 3 in conversation