Desktop productivity for business analysts and programmers

How to use number of rows as comparison variable

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

How to use number of rows as comparison variable

[ Edited ]

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


Accepted Solutions
Solution
‎10-19-2017 10:51 AM
Super User
Super User
Posts: 9,840

Re: How to use number of rows as comparison variable

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.

View solution in original post


All Replies
Solution
‎10-19-2017 10:51 AM
Super User
Super User
Posts: 9,840

Re: How to use number of rows as comparison variable

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.

New Contributor
Posts: 2

Re: How to use number of rows as comparison variable

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

Super User
Posts: 10,571

Re: How to use number of rows as comparison variable

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,571

Re: How to use number of rows as comparison variable

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 462 views
  • 4 likes
  • 3 in conversation