Create a random sample using proc sql

[Nehcour0420]

Hi,

I am having a problem with creating a random sample using proc sql which required to include some certain rows.

For example:

Libname newlib '/sas/sasdata/newlib';

PROC SQL;

CREATE TABLE NEWLIB.NEWTABLE * FROM OLDLIB.OLDTABLE AS OLDTABLE

WHERE OLDTABLE .VARIABLE_A IN (1,2,3,4,5);

QUIT;

How can I add 10 more rows of random sample into NEWTABLE from OLDTABLE?

Thanks,

Eric    

[Linlin]

Hi Eric,

try this:

proc sql outobs=10;

create table random10 as

   select *,ranuni(99) as nn from sashelp.class

     where age in  (11,12,15)

         order by nn;

quit;

proc print;run;

obs    Name       Sex    Age    Height    Weight       nn

  1    Jane        F      12     59.8       84.5    0.03878

  2    William     M      15     66.5      112.0    0.11254

  3    Joyce       F      11     51.3       50.5    0.23866

  4    James       M      12     57.3       83.0    0.31129

  5    Janet       F      15     62.5      112.5    0.58261

  6    Thomas      M      11     57.5       85.0    0.59009

  7    Robert      M      12     64.8      128.0    0.63748

  8    John        M      12     59.0       99.5    0.68047

  9    Mary        F      15     66.5      112.0    0.75494

10    Ronald      M      15     67.0      133.0    0.92961

[Nehcour0420]

Thanks Linlin, however, the problem is that in my case is that the VARIABLE_A in OLDTABLE is an unique key, so if I do it in your way, the result table will still only have 5 rows where VARIABLE_A IN (1,2,3,4,5)

[Linlin]

Sorry, I don't understand your question. Would you please post some sample data and output you want?

[Nehcour0420]

VARIABLE_A VARIABLE_B VARIABLE_C

1                    aa                 123

2                    aa                 235

3                    bb                 453

4                    dd                 111

5                    ss                 123

6                    sf                  234

7                    ad                 234

8                    ae                 675

9                    ss                 222

10                  ss                 123

11                  ee                 877

.

.

.

Well, the data in OLDTABLE is pretty much like this, you can see that VARIABLE_A in this table is the unique key, there is no duplication for it.

What I want in the NEWTABLE is VARIABLE_A in (1,2,3,4,5) PLUS 10 more random sample.

[Linlin · Accepted solution]

/This one doesn't work */

try this one:

data have;
input VARIABLE_A :VARIABLE_B :$2. VARIABLE_C ;
cards;
1                    aa                 123
2                    aa                 235
3                    bb                 453
4                    dd                 111
5                    ss                 123
6                    sf                  234
7                    ad                 234
8                    ae                 675
9                    ss                 222
10                  ss                 123
11                  ee                 877
12                    aa                 123
13                    aa                 235
14                   bb                 453
15                    dd                 111
16                    ss                 123
17                    sf                  234
18                    ad                 234
19                   ae                 675
20                    ss                 222
21                  ss                 123
22                  ee                 877

[Nehcour0420]

It works. Thx a lot!

[Linlin]

Sorry. try this one:

data have;

input VARIABLE_A :VARIABLE_B :$2. VARIABLE_C ;

cards;

1                    aa                 123

2                    aa                 235

3                    bb                 453

4                    dd                 111

5                    ss                 123

6                    sf                  234

7                    ad                 234

8                    ae                 675

9                    ss                 222

10                  ss                 123

11                  ee                 877

12                    aa                 123

13                    aa                 235

14                   bb                 453

15                    dd                 111

16                    ss                 123

17                    sf                  234

18                    ad                 234

19                   ae                 675

20                    ss                 222

21                  ss                 123

22                  ee                 877

23                  ss                 123

24                   bb                 453

25                    dd                 111

26                    ss                 123

27                    sf                  234

28                    ad                 234

29                   ae                 675

;

proc sql outobs=10;

create table random10  as

   select * from have

     where variable_a not in (1,2,3,4,5)

      order by ranuni(99);

reset outobs=15;

     create table random10_15  as

select * from have

     where variable_a in (1,2,3,4,5)

       union all

    select * from random10;

quit;

proc sort data=random10_15;by variable_a;

proc print;run;