Get line number in data step using sas

Question

Is there any way to do over a section to get the line number on sas? In sql, I would like:

Select region,company, ROW_NUMBER() OVER(PARTITION BY region ORDER BY Name) From companyData; 

I want to do this in a dataset preferably

Answer 1

You can easily do this at the data step using the by operator, and it will execute the current amount:

proc sort data=myData; by region name; run;

 Data myData; Set myData; By company; if first. company then n=1; else n+1; run; 

In addition, to explain the whole obs, you can use the built-in functions:

 DATA COMPANYDATA; SET COMPANYDATA; ROW_NUM=_N_; RUN; 

As Joe said, you can set the format for your row_num depending on how many obs you get in this group.

Answer 2

To duplicate the SQL ROW_NUMBER Windowing function, you must sort the dataset using the PARTITION and ORDER BY clauses. In the next data step, the SET sorted data set using only the partition variable (s) and uses the automatic control of the FIRST. variable FIRST. , assigns a new line number variable.

Here is the solution for your claimed example:

 proc sort data=companyData; by region name; run; data want; set companyData; by region; if first.region then row_number = 1; else row_number + 1; run; 

Note that the BY in the data step corresponds to the PARTITION BY in ANSI SQL, but the PROC SORT step is also sorted by column in the ORDER BY . Also note that if the query you are doing the “transfer” contains an ORDER BY SQL ORDER BY , you must do this with another PROC SORT step.