Desktop productivity for business analysts and programmers

subsetting data set due to no of obs in another one

Reply
Occasional Contributor
Posts: 6

subsetting data set due to no of obs in another one

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
SAS Employee
Posts: 105

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

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 #
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
Occasional Contributor
Posts: 17

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

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

check the manual of SURVEYSELECT
Ask a Question
Discussion stats
  • 2 replies
  • 131 views
  • 0 likes
  • 3 in conversation