BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Nehcour0420
Calcite | Level 5

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    

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

/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

7 REPLIES 7
Linlin
Lapis Lazuli | Level 10

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
Calcite | Level 5

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
Lapis Lazuli | Level 10

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

Nehcour0420
Calcite | Level 5

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
Lapis Lazuli | Level 10

/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
Calcite | Level 5

It works. Thx a lot!

Linlin
Lapis Lazuli | Level 10

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

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!
How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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