topic Re: creating SAS dataset in SAS Programming

creating SAS dataset

Rohit12 — Tue, 20 Dec 2016 09:26:19 GMT

I have a dataset and I want one fourth of observation to go in one dataset and another one fouth to a different dataset and another one fourth to different dataset and so on .How can i do this

Re: creating SAS dataset

gamotte — Tue, 20 Dec 2016 09:33:56 GMT

Hi,

If there is no particular criterion to select which data goes where, the following program should do what you want.

data want1 want2 want3 want4;
set sashelp.class;
if mod(_N_,4)=0 then output want1;
if mod(_N_,4)=1 then output want2;
if mod(_N_,4)=2 then output want3;
if mod(_N_,4)=3 then output want4;
run;

Re: creating SAS dataset

Rohit12 — Tue, 20 Dec 2016 09:44:10 GMT

what i want first four observation should go to want1 dataset and then next four to want2 and so on

Re: creating SAS dataset

gamotte — Tue, 20 Dec 2016 09:57:17 GMT

I will go with a macro (sorry RW9)

%macro create_data;
	proc sql;
		SELECT ceil(count(*)/4)
		INTO :nbdatasets
		FROM sashelp.class;
	quit;

	%let datasets=;

	%do i=1 %to &nbdatasets.;
	   %let datasets=&datasets want&i.;
	%end;

	data &datasets.;

		set sashelp.class;

		%do i=1 %to &nbdatasets.;
		    if ceil(_N_/4)=&i. then output want&i.;
		%end;

	run;
%mend;

%create_data;

Re: creating SAS dataset

RW9 — Tue, 20 Dec 2016 10:47:13 GMT

Seeing as you invoked my macros aversions

data _null_;
  set sashelp.vtable (where=(libname="WORK" and memname="<yourds>"));
  do i=1 to nobs by 4;
    call execute(cats('data want',put(i,best.),'; set work.<yourds> point=',put(i,best.),' obs=4; run;'));
  end;
run;

That being said however, I would really question the need to split your data up in the first place. The question is why? SAS is built around the concept of by group processing, each of the functions are optimised to do thing over groups. Read/write to dataset is one of the biggest resource hogs in most proceses. So why not in your one dataset assign a group value, e.g. a number which starts at 1 and then increment by one each 4 observations. You can then do:

proc means data=have;
  by group;
  ...

For example to get a means for each group. Its also far simpler code.

Re: creating SAS dataset

Patrick — Tue, 20 Dec 2016 10:17:19 GMT

A variation of @gamotte initial post which splits up the data as per your requirement.

data want1 want2 want3 want4;
  set sashelp.class;
  select ( mod(floor((_N_-1)/4),4) );
    when (0) output want1;
    when (1) output want2;
    when (2) output want3;
    when (3) output want4;
    otherwise;
    end;
run;

Re: creating SAS dataset

gamotte — Tue, 20 Dec 2016 10:20:01 GMT

Didn't you mean dictionary.columns rather than sashelp.class ?

Re: creating SAS dataset

Ksharp — Tue, 20 Dec 2016 10:37:27 GMT

%let dsid=%sysfunc(open(sashelp.class));
%let nobs=%sysfunc(attrn(&dsid,nlobs));
%let n=%sysevalf(&nobs/4,i);
%let dsid=%sysfunc(close(&dsid));

data want1 want2 want3 want4;
 set sashelp.class;
 if _n_ le &n then output want1;
  else if _n_ le 2*&n then output want2;
   else if _n_ le 3*&n then output want3;
    else output want4;
run;

Re: creating SAS dataset

Ksharp — Tue, 20 Dec 2016 10:45:58 GMT

If you want randomly select obs ,try proc surveyselect.

proc surveyselect data=sashelp.class groups=4 out=temp;
run;
data want1 want2 want3 want4;
 set temp;
 if groupid=1 then output want1;
  else if groupid=2 then output want2;
   else if groupid=3 then output want3;
    else output want4;
run;

Re: creating SAS dataset

RW9 — Tue, 20 Dec 2016 10:48:00 GMT

Nah, I meant sashelp.vtable (corrected now) or you could use dictionary.tables.

Re: creating SAS dataset

Rohit12 — Tue, 20 Dec 2016 11:23:33 GMT

this code will work for sashelp.class but what If my dataset is having 1000 rows then it will create 250 datasets .But what I want is 4 datasets only

Re: creating SAS dataset

RW9 — Tue, 20 Dec 2016 11:36:48 GMT

Sorry, hard to tell if thats a response to my code, so will add. If you only want four tables then @Patrick has a good solution, you just need to fiddle with the select part. However still better to assign groups in the one dataset, and then use by group processing.

Re: creating SAS dataset

Rohit12 — Tue, 20 Dec 2016 11:47:35 GMT

I have a dataset which has 1000 rows and I want 4 different datasets to be created with each having 250 rows for ex want1 should have rows from 1 to 250 and want 2 to have from 251 to 500

will your code wok ?

Re: creating SAS dataset

Kurt_Bremser — Tue, 20 Dec 2016 13:13:44 GMT

@Rohit12 wrote:

will your code wok ?

TRY.IT.

Re: creating SAS dataset

gamotte — Tue, 20 Dec 2016 13:46:15 GMT

@Ksharp's solution seems to be what you want.

In order to get correct answers, you should put a little more effort in explaining exactly what is the desired output because :
"I have a dataset and I want one fourth of observation to go in one dataset and another one fouth to a different dataset and another one fourth to different dataset and so on"
and
" what i want first four observation should go to want1 dataset and then next four to want2 and so on"
seem to be two completely different demands.

"want1 should have rows from 1 to 250 and want 2 to have from 251 to 500" is a precision you should have made in your first post.

You'll get better answers quicker and it will avoid unnecessary work to contributors.

Re: creating SAS dataset

ballardw — Tue, 20 Dec 2016 15:17:38 GMT

If you want to specify specific sequences of observations here is one way:

 data junk1 ;
 set have(firstobs=1 obs=250 );
 run;

 data junk2 ;
 set have(firstobs=251 obs=500);
 run;

but the question does beg for an answer of what gets done with an apparently arbitrary number of records.

Re: creating SAS dataset

s_lassen — Tue, 20 Dec 2016 17:11:51 GMT

With an arbitrary number of records, the best way may be to find the number of records first, e.g.:

data _null_;
  call symputx('nobs',nobs);
  stop;
  set have nobs=nobs;
run;

data want1;
  set have(obs=%eval(&nobs/4));
run;

data want2;
  set have(firstobs=%eval(&nobs/4+1) obs=%eval(&nobs/2));
run;

data want3;
  set have(firstobs=%eval(&nobs/2+1) obs=%eval(3*&nobs/4));
run;

data want4;
  set have(firstobs=%eval(3*&nobs/4+1));
run;

As shown, it needs a little familiarity with SAS macro language. Which is hard to avoid if you want to do this kind of "advanced" stuff.

Re: creating SAS dataset

SuryaKiran — Tue, 20 Dec 2016 18:00:36 GMT

Hope this macro program will be helpful.

%MACRO test(lib=,ds=);

proc sql;

select count(*) INTO: nobs

from &lib..&ds. ;

Quit;

%put &nobs;

%do i=0 %to &nobs. %by %SYSFUNC(CEIL(&nobs./4)) ;

data out&i;

set &lib..&ds.(firstobs=%EVAL(&i+1) obs=%EVAL((&nobs./4)+&i) ) ;

run;

%end;

%mend;

%test(lib=sashelp,ds=class);