topic How to create a sampled table from an original table ? in SAS Programming

How to create a sampled table from an original table ?

Midi — Mon, 23 Sep 2019 08:20:31 GMT

Hello , I have a tabe that contains 7 million rows and i want to sample it , by keeping only (700.000) rows :

OBS	Id_Cust	Cus_Id_Dup	NaT_Id	Gen_Id	Registr_Dat_Id	Sign_Dat_NL_Id	Dat_Firs_Aqui_Id	DaT_Last_acqu_Id	N_Ord	aaau_Id	a_id	GR_id	LK	LK1	ss_id	CC	D_ID	D_N
1	238517	238517	60	1	2003	0	0	0	0	6523902	.	0	0	0	0	N/A	4	01FEB1997:10:00:00
2	238918	235518	70	1	2004	0	0	0	0	6904002	.	0	0	0	0	N/A	21	.
3	235519	235519	45	1	2004	0	6545	6545	0	6905202	.	0	0	0	0	N/A	21	.
4	238520	238920	6	1	2001	0	0	0	0	6905202	.	0	0	0	0	N/A	5	20GEN1993:11:00:00
5	238521	238521	90	1	2000	0	0	0	0	6524302	.	0	0	0	0	N/A	5	.
6	238922	238922	11	1	2000	0	0	0	0	6905402	.	0	0	0	0	N/A	5

So This is What I tried , without any Success :

proc sql;
	create table clients as 
	select Id_Cus, ranuni(66564) as random_num
	(select distinct ID_Cus
	from dbo.Eu_Ord)
	where calculated random_num<=0.05
	;
quit;

Any Help Would be Much Appreciated , thank you all.

Re: How to create a sampled table from an original table ?

subhroster — Mon, 23 Sep 2019 09:05:56 GMT

You can sample number of rows from existing data set using the Proc Survey Select .

proc surveyselect data=dbo.Eu_Ord
   method=srs n=700 out=clients;
run;

Re: How to create a sampled table from an original table ?

FreelanceReinh — Mon, 23 Sep 2019 09:44:21 GMT

Hello @Midi,

First of all, you should start with a clear picture of what type of sample you need:

What is the sampling unit? If it is specified by Id_Cust, is this a unique key in the original table?
Do you need exactly 700,000 rows or exactly 10 % (i.e. round(N/10) where N is the number of rows in the original table) or would just "about 10 %" be acceptable?
Do you need a sample with replacement or without replacement? (see explanations in last week's thread https://communities.sas.com/t5/SAS-Programming/How-to-select-10-random-rows-in-a-data-set/m-p/589518#M168633)
Do you need all variables from the original table in the sample or only Id_Cust and (really?) a random number?
Would you prefer the sample to be in random order or in the same order as in the original table?

The next question is: Do you have SAS/STAT licensed so that PROC SURVEYSELECT (as suggested by @subhroster) is available?