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
/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
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
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)
Sorry, I don't understand your question. Would you please post some sample data and output you want?
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.
/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
It works. Thx a lot!
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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.