SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Create a random sample using proc sql

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

Create a random sample using proc sql

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    


Accepted Solutions
Solution
‎05-10-2012 09:32 AM
Super Contributor
Posts: 1,636

Re: Create a random sample using proc sql

/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

View solution in original post


All Replies
Super Contributor
Posts: 1,636

Re: Create a random sample using proc sql

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

Contributor
Posts: 25

Re: Create a random sample using proc sql

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)

Super Contributor
Posts: 1,636

Re: Create a random sample using proc sql

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

Contributor
Posts: 25

Re: Create a random sample using proc sql

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.

Solution
‎05-10-2012 09:32 AM
Super Contributor
Posts: 1,636

Re: Create a random sample using proc sql

/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

Contributor
Posts: 25

Re: Create a random sample using proc sql

It works. Thx a lot!

Super Contributor
Posts: 1,636

Re: Create a random sample using proc sql

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;

☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 5513 views
  • 3 likes
  • 2 in conversation