create multiple subsets from big dataset

[yashpande]

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

[PGStats · Accepted solution]

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

[Jagadishkatam]

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,

Jagadish