🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Obsidian | Level 7

## create multiple subsets from big dataset

Hi all,

I have a huge dataset  of  1000000 observations which we need to analyse and the analysts are adamant on using excel. So I need to create smaller subsets of this. I know of firstobs and obs options but somehow using that in a macro is not working for me.

I have used following code but its not producing correct output so far. Any help is really appreciated.

%macro M1(fobs,ob,ds);

options fobs=&fobs obs=&ob;

data &ds;

set have;

run;

%mend;

%m1(1,10000,new_1)

%m1(10001,10000,new_2)

%m1(20001,10000,new_3)

%m1(30002,10000,new_4)

%m1(40003,10000,new_5)

%m1(50004,10000,new_6)

%m1(60005,10000,new_7)

%m1(70006,10000,new_8)

%m1(80007,10000,new_9)

%m1(90008,10002,new_10)

Any help is appreciated. also, is there any proc sql help available for such task

1 ACCEPTED SOLUTION

Accepted Solutions
Opal | Level 21

## Re: create multiple subsets from big dataset

How about (untested) :

data d1 d2 d3 d4 d5 d6 d7 d8 d9 d10;

set have;

select (ceil(_n_/10000));

when(1) output d1;

when(2) output d2;

when(3) output d3;

when(4) output d4;

when(5) output d5;

when(6) output d6;

when(7) output d7;

when(8) output d8;

when(9) output d9;

otherwise output d10;

end;

run;

PG

PG
2 REPLIES 2
Opal | Level 21

## Re: create multiple subsets from big dataset

How about (untested) :

data d1 d2 d3 d4 d5 d6 d7 d8 d9 d10;

set have;

select (ceil(_n_/10000));

when(1) output d1;

when(2) output d2;

when(3) output d3;

when(4) output d4;

when(5) output d5;

when(6) output d6;

when(7) output d7;

when(8) output d8;

when(9) output d9;

otherwise output d10;

end;

run;

PG

PG
Amethyst | Level 16

## Re: create multiple subsets from big dataset

The option fobs=&fobs is in correct , it should be firstobs=

the value of the firstobs= should be the starting observation number and that of obs= should be the observation number till where you need to output the data.

So i have modified your macro the below way, please try

%macro M1(fobs,ob,ds);

options firstobs=&fobs obs=&ob;

data &ds;

set have;

run;

%mend;

%m1(1,10000,new_1)

%m1(10001,20000,new_2)

%m1(20001,30001,new_3)

%m1(30002,40002,new_4)

%m1(40003,50003,new_5)

%m1(50004,60004,new_6)

%m1(60005,70005,new_7)

%m1(70006,80006,new_8)

%m1(80007,90007,new_9)

%m1(90008,100002,new_10)

Thanks,