topic Re: Create a random sample using proc sql in SAS Data Management

Create a random sample using proc sql

Nehcour0420 — Thu, 10 May 2012 12:23:47 GMT

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

Re: Create a random sample using proc sql

Linlin — Thu, 10 May 2012 12:44:17 GMT

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

Re: Create a random sample using proc sql

Nehcour0420 — Thu, 10 May 2012 12:52:01 GMT

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)

Re: Create a random sample using proc sql

Linlin — Thu, 10 May 2012 13:05:54 GMT

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

Re: Create a random sample using proc sql

Nehcour0420 — Thu, 10 May 2012 13:15:00 GMT

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.

Re: Create a random sample using proc sql

Linlin — Thu, 10 May 2012 13:32:18 GMT

/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

Re: Create a random sample using proc sql

Nehcour0420 — Thu, 10 May 2012 13:54:41 GMT

It works. Thx a lot!

Re: Create a random sample using proc sql

Linlin — Thu, 10 May 2012 14:22:59 GMT

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;