topic Re: How to use number of rows as comparison variable in SAS Enterprise Guide

How to use number of rows as comparison variable

Lottek — Fri, 20 Oct 2017 12:33:55 GMT

Hi,

I'm a beginner SAS user. I have a dataset with N number of rows/observations. Then I have a separate, dataset with a column called Rank, this second dataset is bigger than the first one.

What I want to do, is I want to cut out all rows from my second dataset with a rank higher than N.

I have this now

DATA Want1 Want2;
SET Have;
IF (Rank > N) THEN OUTPUT Want1;
IF (Rank <= N) THEN OUTPUT Want2;
RUN;

But I don't know how to make N. How do get the Number of Rows (or number of URNs, if it's easier with a defined column name) to use here?

Thanks,
Lotte

ps I'm using SAS Enterprise Guide 7.13

Re: How to use number of rows as comparison variable

RW9 — Thu, 19 Oct 2017 14:14:49 GMT

Well, one way:

data _null_;
  set sashelp.vtable (where=(libname="WORK" and memname="HAVE"));
  call symput('N',nobs);
run;

data want1 want2;
  set have;
  if ... > &n. then output want1;
  else output want2;
run;

Really not sure about your if logic of rank > N? Maybe provide some test data and required output to clarify.

Re: How to use number of rows as comparison variable

Kurt_Bremser — Thu, 19 Oct 2017 14:16:49 GMT

Look at sashelp.vtable (or dictionary.tables in PROC SQL). The number of observations is found in column nobs.

proc sql;
select nobs into :n from dictionary.tables where libname = 'YOURLIB' and memname = 'YOURDATASET';
quit;

Another way is this:

data _null_;
call symput('n',put(number,best.));
set yourlib.yourdataset nobs=number;
stop;
run;

Re: How to use number of rows as comparison variable

Lottek — Fri, 20 Oct 2017 10:49:13 GMT

Thank you for your answer!

This worked when my dataset with N observations had observations in it. When it's empty, my variable N is empty and it doesn't run. Is there a way to tell it that if N is null, put 0?

Thanks,

Lotte

Re: How to use number of rows as comparison variable

Kurt_Bremser — Fri, 20 Oct 2017 11:04:03 GMT

The placement of statements in the data step is crucial. Note where @RW9 put his call symput, and where I put mine.

Slightly expanded version of my code to show the effect:

data class;
set sashelp.class (obs=0);
run;

data _null_;
call symput('n',put(number,best.));
put "in one iteration";
set work.class nobs=number;
stop;
run;

%put n=&n;

Log from this:

24         data class;
25         set sashelp.class (obs=0);
26         run;

NOTE: There were 0 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.CLASS has 0 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      

27         
28         data _null_;
29         call symput('n',put(number,best.));
30         put "in one iteration";
31         set work.class nobs=number;
32         stop;
33         run;

in one iteration
NOTE: There were 0 observations read from the data set WORK.CLASS.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

34         
35         %put n=&n;
n=           0

Note that this was run on a pristine SAS session to make sure that &n. was not left from a previous run.

Now I move the set statement to the top in my data _null_, and this is the log:

24         data class;
25         set sashelp.class (obs=0);
26         run;

NOTE: There were 0 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.CLASS has 0 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

27         
28         data _null_;
29         set work.class nobs=number;
30         call symput('n',put(number,best.));
31         put "in one iteration";
32         stop;
33         run;

NOTE: There were 0 observations read from the data set WORK.CLASS.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

34         
35         %put n=&n;
WARNING: Apparent symbolic reference N not resolved.
n=&n

(once again on a fresh SAS session)

The call symput (and the put statement I inserted for debugging) is never reached, as the EOF that happens at the set statement immediately terminates the data step.

If you are puzzled why the nobs= variable is available and set before the set statement: This variable is initialized with the nobs value before the first iteration even starts; you could see this as a declarative characteristic of the set statement.