Erosennin Erosennin - 18 days ago 6
SQL Question

Proc SQL: inobs and outobs not random

It seems to me that when I re-run an proc sql code in SAS, and I use the inobs= and the outobs= options, they give me the same rows every time. This leads me to conclude that inobs= and outobs= just give first rows available. Is this true?
Is there an option like outobs= that will give me outobs=n random rows?

Answer

I think the closest thing you can easily do within proc sql itself is something like this. It won't give you a precise number of rows, but it should get you roughly the right number provided that you know in advance how many rows there are in each of the source tables.

/*20% random sample*/
proc sql;
  create table example as 
    select * from sashelp.class(where=(ranuni(1) < 0.2));
quit;

Otherwise, as Reeza has suggested, take samples from your datasets via proc surveyselect or some other method and pass those into proc sql.

I'd suggest using where clauses within individual dataset options, so that you can leave the rest of your query unchanged, including any existing where clauses, but there are various ways you could do this.