topic Re: How to read number of observation by variables in SAS Programming

How to read number of observation by variables

Hhh111 — Wed, 18 Sep 2019 15:53:46 GMT

Hi all,

I need opinions on how to read number of observation based on different customer. For example, my data as per below

Dataset A
Customer| customer no | count
A | 111 | 3
B | 222 | 2

Dataset B (Main)
Customer | customer no | sales
A | 111 | 12000
A | 111 | 2000
A | 111 | 10000
A | 111 | 7000
B | 222 | 1000
B | 222 | 5000
B | 222 | 6500

In dataset A, the count indicates how many observation i need to extract from dataset B (Main dataset). So i dont know how to randomly select the no of observation by each customer as shown above.

Thanks.

Re: How to read number of observation by variables

PeterClemmensen — Wed, 18 Sep 2019 15:56:59 GMT

So in this case, you want to randomly pick 3 obs from group Customer=A and 2 obs from Customer=B?

Re: How to read number of observation by variables

PaigeMiller — Wed, 18 Sep 2019 16:37:04 GMT

The general method would be to assign a random number to each record, then sort by customer number and that random number, then select the first COUNT records of each Customer number.

Something liek this:

data both;
     merge a b;
    by customer_no;
    random_no=rand('uniform');
run;
proc sort data=both;
    by customer_no random_no;
run;
data want;
    set both;
    by customer_no;
    if first.customer_no then seq=0;
    seq+1;
    if seq<=count;
run;

Re: How to read number of observation by variables

ErikLund_Jensen — Wed, 18 Sep 2019 16:57:13 GMT

Hi @Hhh111

One way to do it is by sorting sales data in random order for each customer and then keep only the first n observarions from each customer, where n is the individual sample size for each customer:

* Get test data 1 - Sample size for each customer;
data sample_no;
	input Customer$ customer_no count;
	cards;
A 111 3
B 222 2
;
run;

* Get test data 2 - Sales data set;
data main;
	input Customer$ customer_no sales;
		cards;
A 111 12000
A 111 2000
A 111 10000
A 111 7000
B 222 1000
B 222 5000
B 222 6500
;
run;

* Add random number to sales data set to use for sorting;
data m2; set main;
	shuffle_id = ranuni(3);
run;

* Join sample size on all records in main and sort on customer - random number;
proc sql;
	create table m3 as 
	select a.Customer, a.customer_no, a.sales, a.shuffle_id, b.count
	from m2 as a inner join sample_no as b
	on a.Customer = b.Customer and a.customer_no = b.customer_no
	order by a.Customer, a.customer_no, a.shuffle_id;
quit;

* Keep only wanted count of records for each customer;
data want (drop=shuffle_id count rcnt);
	set m3;
	retain rcnt;
	by Customer customer_no;
	if first.customer_no then rcnt = 0;
	rcnt = rcnt + 1;
	if rcnt <= count then output;
run;

Re: How to read number of observation by variables

ErikLund_Jensen — Wed, 18 Sep 2019 17:03:14 GMT

Oh, didn't see the answer from PaigeMiller. Basically the same, but he came up with a more elegant solution with two steps only!

Re: How to read number of observation by variables

Reeza — Wed, 18 Sep 2019 17:22:19 GMT

PROC SURVEYSELECT has a sampsize option where you need to provide the sizes as a data set or values. I think you should be using that here instead of doing it manually via a data step.

Re: How to read number of observation by variables

PeterClemmensen — Wed, 18 Sep 2019 18:14:34 GMT

Data A;
input Customer $ customerno count;
datalines;
A 111 3
B 222 2
;

data B;
input Customer $ customerno sales;
datalines;
A 111 12000
A 111 2000
A 111 10000
A 111 7000
B 222 1000
B 222 5000
B 222 6500
;

data want(keep=Customer customerno sales);
	if _N_=1 then do;
		declare hash h();
		h.definekey('p');
		h.definedone();
	end;

	do _N_=1 by 1 until (last.Customer);
		set B;
		by Customer;
	end;

    set A;

	do until (c=count);
		p=rand('integer', 1, _N_);
		if h.check() ne 0 then do;
			n=_iorc_+p;
			set B point=n;
			output;
			c+1;h.ref();
		end;
	end;
	h.clear();
	_iorc_+count+1;
	c=0;
run;

Re: How to read number of observation by variables

PeterClemmensen — Wed, 18 Sep 2019 19:38:01 GMT

This is the simplest though

proc surveyselect data=B sampsize=A(rename=(count=_NSIZE_)) 
				      out=want(keep=Customer customerno sales) method=srs noprint;
   	strata Customer;
run;

Re: How to read number of observation by variables

ballardw — Wed, 18 Sep 2019 21:16:47 GMT

@PeterClemmensen wrote:

This is the simplest though

proc surveyselect data=B sampsize=A(rename=(count=_NSIZE_)) 
				      out=want(keep=Customer customerno sales) method=srs noprint;
   	strata Customer;
run;

And if the Customer value might repeat then add the Customerno to the strata statement.

Both data sets need to be sorted by the variables on the strata statement.

Re: How to read number of observation by variables

Hhh111 — Thu, 19 Sep 2019 00:44:34 GMT

Yes!!

Re: How to read number of observation by variables

Hhh111 — Thu, 19 Sep 2019 02:47:32 GMT

Thanks All for the response...i get the solution already