topic How to separate a table in SAS based on rows? in SAS Programming

How to separate a table in SAS based on rows?

JQian — Mon, 13 Feb 2023 17:53:25 GMT

Hi all,

I want to separate a table into 2 tables. For example, the original table has 1000 rows. I want to move the first 500 rows into table A and the last 500 rows into Table B. Does anyone know what code can perform this? Thank you for helping.

Re: How to separate a table in SAS based on rows?

PaigeMiller — Mon, 13 Feb 2023 17:56:48 GMT

data one two;
    set have;
    if _n_<=500 then output one;
    else output two;
run;

Re: How to separate a table in SAS based on rows?

Tom — Mon, 13 Feb 2023 17:57:20 GMT

So you want to make two new datasets from one existing dataset? Just name both target datasets on the DATA statement and then explicitly output the observations you want to each dataset.

For example to put the first 500 observations from HAVE into A and the rest into B you could do a data step like this:

data a b;
  set have;
  if _n_ <= 500 then output a;
  else output b;
run;

Re: How to separate a table in SAS based on rows?

JQian — Mon, 13 Feb 2023 18:06:57 GMT

Hi Tom,

Thanks for the quick reply. What if I want row 2 to row 10 out to a new table? Is it

if (_n_ >= 2 and _n_ <= 10) then output newtable;

Thanks

Re: How to separate a table in SAS based on rows?

ballardw — Mon, 13 Feb 2023 18:18:23 GMT

@JQian wrote:

Hi Tom,

Thanks for the quick reply. What if I want row 2 to row 10 out to a new table? Is it

if (_n_ >= 2 and _n_ <= 10) then output newtable;

Thanks

Yes.

or simplier:

if 2 le _n_ le 10 then ...

SAS will allow comparison more that way. I use the LE instead of <= because of some keyboard issues from many years ago.

or since we deal with integers

if _n_ in (2:10) then ...

The IN operator when comparing integers will accept the colon to indicate start and end of a range of values. This might be the preferred way if you wanted to do some skips and multiple ranges such as:

if _n_ in (2:10 22:30 41:66) then ...

Re: How to separate a table in SAS based on rows?

Tom — Mon, 13 Feb 2023 18:19:27 GMT

Should work. If you just want to make one dataset you can also use the OBS= and FIRSTOBS= dataset option on the input dataset.

data want;
  set have (obs=10 firstobs=2);
run;

But doing the selection based on the order of the rows is going to be much riskier than doing it based on some value in the data.