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?
Thank you kindly.
Cheers,
Yeti
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.
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
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.
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:
The WANT dataset contains:
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
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
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
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.
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.
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.
Hey yeti,
how were you able to separate len2 from ndiff? How did you find out the length of len2?
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: