get a the row number in a data step using sas

问题:

Is there a way to get to do a over partition to get the row number on sas? In sql I would go like:

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

I want to do this in a data set preferably

回答1:

You can do that in a data step easily by using the by statement, it and the do a running sum :

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

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

Also to ennmuarete all the obs you can use the built in features:

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

As Joe mentioned you might want to set the format for your row_num depending on how much obs you would get within that grouping.

回答2:

To duplicate the SQL ROW_NUMBER Windowing function, you must sort the data set by both the PARTITION and ORDER BY clauses. In a subsequent data step, SET the sorted data set only by the partition variable(s) and use the FIRST. automatic variable control assign the new row number variable.

Here is a solution for your stated 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;

Notice the BY statement in the data step corresponds to the PARTITION BY clause in ANSI SQL but the PROC SORT step also sorts by the columns in the ORDER BY clause. Also note that if the query you are "migrating" contains an ORDER BY SQL clause, you must follow this up with another PROC SORT step.