Tag Archives: number

Creating Row Numbers with Proc SQL

Some of you may be familiar with the Proc SQL NUMBER option. This option displays row/observation numbers with a report; however, it does not store these row numbers in a dataset. In order to create and store these row numbers, use the MONOTONIC() function. Here’s an admissions-related example of using MONOTONIC() with PROC RANK to create a list of unique procedure codes ranked by procedure type for each case id:

/*create listing of unique procedure codes*/
proc sql;
create table unique_procs as
select monotonic() as row,
sq.* from
(select
case_id,
mbr_num,
svc_dt,
proc_cd,
midsu_proc_mdfr_cd,
proc_ty,
case when proc_ty IN(‘4′,’5′,’RV’)then proc_cd end as rev_proc,
case when proc_ty in (‘7′,’HC’) then proc_cd end as hcpcs_proc,
case when proc_ty IN(‘1′,’CP’) then proc_cd end as cpt_proc,
case when proc_ty IN(‘9′,’IC’) then proc_cd end as icd9_proc
from work.ppo_ip_claim_all_&mkt
group by
case_id,
mbr_num,
svc_dt,
proc_cd,
midsu_proc_mdfr_cd,
proc_ty
)sq
order by
case_id,
proc_ty
;
quit;

/*number fields*/
proc rank data=work.unique_procs out=work.unique_procs_ranked ties=low;
by case_id proc_ty;
var row;
ranks ln_num;
run;