topic Re: subsetting data set due to no of obs in another one in SAS Enterprise Guide

subsetting data set due to no of obs in another one

Mohamed — Sun, 03 Apr 2011 10:51:24 GMT

Dear,

I have two data set as follow:

one ID CNT
1 2
2 1

Two
ID Qnty Price

1 50 20
1 15 7
1 18 40
1 14 80
2 18 70
2 15 41

how can I select number of observations from data set two as per CNT from data set one. it means I want to select only two observations from data set two has ID 1. and only 1 observation from data set two has ID 2 Message was edited by: Mohamed

Re: subsetting data set due to no of obs in another one

yonib — Mon, 04 Apr 2011 13:00:30 GMT

Hi,
From Your description i dont understand, for example how you choose the two observations from data set two has ID 1???
In my soulotion i assumed you want to choose the first onces:

options symbolgen mprint;

data one;
input ID CNT;
cards
;
1 2
2 1
;
run;
*count the numbers of id's in table one;
proc sql noprint;
select count(*)
into: num
from one
;
quit;

proc sql noprint;
select id,cnt
into: var1-:var%sysfunc(compress(&num)),:cnt separated by '*'
from one
;
quit;

data Two;
input ID Qnty Price;
cards;
1 50 20
1 15 7
1 18 40
1 14 80
2 18 70
2 15 41
;
run;

%macro example;
%do i=1 %to &num;
proc sql;
create table _tosplit_&i as
select *
from two
where ID=&&var&i
;
quit;

data _tossplit_&i ;
set _tosplit_&i (obs=%scan(&cnt,&i,*));
run;
%if &i=1 %then %do;
data all;
set _tossplit_&i;
run;
%end;
%else %do;
proc append base=all
data=_tossplit_&i
;
run;
%end;

%end;

%mend examle;
%example;

Good Luck

Re: subsetting data set due to no of obs in another one

oloolo — Mon, 04 Apr 2011 15:00:18 GMT

change the variable name CNT in data one appropriately, and use PROC SURVEYSELECT with strata

check the manual of SURVEYSELECT