topic Random select multiple groups without duplicates in SAS Enterprise Guide

Random select multiple groups without duplicates

HKlaassen — Wed, 08 May 2013 11:52:36 GMT

, I want to send a survey to our customers based on a random selection, but i'am stuck.

The problem is that a customers can appear within multiple categories (like customerid 1 and 2 in the example below) what can lead to duplicates in the random selection

What i need is a example how to realise a random selection which selects random three customers a category without duplicaties.

I'm using Enterprise Guide 4.3

input example

category 1	customerid
	1
	2
	3
	4
	5
category 2	customerid
	1
	2
	8
	7
	10

output example

category 1	customerid
	1
	2
	3
category 2	8
	9
	7

Re: Random select multiple groups without duplicates

Doc_Duke — Thu, 09 May 2013 18:06:22 GMT

One approach would be:

Assign a pseudo-random number between 0 and 1 (the RANUNI function) to every record.

Sort by customerid and that number (the category will now be in a random order within customerid).

Select the first of each customerid (now have one record per customerid).

Re-sort by category and the random number (the customerids will now be in a random order within category).

Select first 3 in each category.

Doc Muhlbaier

Duke

Re: Random select multiple groups without duplicates

HKlaassen — Tue, 14 May 2013 07:02:26 GMT

Thanks for your reply.

I only have one more question; how do I select the first 3 rows by each category?

Harm Klaassen

Re: Random select multiple groups without duplicates

Doc_Duke — Tue, 14 May 2013 14:56:18 GMT

Harm,

I would usually use a DATA step and a retain statement for that. Something like

DATA want;

SELECT have;

BY category;

RETAIN counter; DROP counter;

IF first.category THEN counter=0;

IF counter <=3 THEN OUTPUT;

RUN;

Doc