turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Enterprise Guide
- /
- How to use number of rows as comparison variable

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-19-2017 09:53 AM - edited 10-20-2017 08:33 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Lottek

10-19-2017 10:14 AM

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.

All Replies

Solution

10-19-2017
10:51 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Lottek

10-19-2017 10:14 AM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-20-2017 06:49 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Lottek

10-20-2017 07:02 AM - edited 10-20-2017 07:04 AM

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

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Lottek

10-19-2017 10:16 AM

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

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code