topic Re: Distinct Row numbers to dataset in SAS Programming

Distinct Row numbers to dataset

Q1983 — Fri, 17 Apr 2020 13:55:48 GMT

data have; input loan_num $ date1 date9.; format date1 date9.; datalines; 111 11jun2019 111 11jul2019 112 11aug2019 113 11sep2019 113 11oct2019 114 11oct2019 ;run; Produces 111 11jun2019 111 11jul2019 112 11aug2019 113 11sep2019 113 11oct2019 114 11oct2019 I need to assign a row number to each distinct Loan_num such as loan_num date1 Row_Num 111 11Jun2019 1 111 11Jul2019 0 112 11Aug2019 1 113 11Sep2019 1 113 11Oct2019 0 114 11Oct2019 1 If the loan_num only appears once, assign a 1. If the loan_num appears twice, assign a 1 to the first one and a 0 to any subsequent loan_num

Re: Distinct Row numbers to dataset

Q1983 — Fri, 17 Apr 2020 13:58:11 GMT

loan_num date1 Row_Num
111 11Jun2019 1
111 11Jul2019 0
112 11Aug2019 1
113 11Sep2019 1
113 11Oct2019 0
114 11Oct2019 1

Re: Distinct Row numbers to dataset

Jagadishkatam — Fri, 17 Apr 2020 14:01:44 GMT

Please try below code

data have; 
input loan_num $ date1 date9.; 
format date1 date9.; datalines; 
111 11jun2019 
111 11jul2019 
112 11aug2019 
113 11sep2019 
113 11oct2019 
114 11oct2019 
;
run; 

proc sort data=have;
by loan_num date1;
run;

data want;
set have;
by loan_num date1;
if first.loan_num then Row_Num=1;
else Row_Num=0;
run;

Re: Distinct Row numbers to dataset

Kurt_Bremser — Fri, 17 Apr 2020 14:47:08 GMT

PLEASE do not (and I mean NOT) post code into the main posting window, or c**p like this happens:

@Q1983 wrote:
data have; input loan_num $ date1 date9.; format date1 date9.; datalines; 111 11jun2019 111 11jul2019 112 11aug2019 113 11sep2019 113 11oct2019 114 11oct2019 ;run; Produces 111 11jun2019 111 11jul2019 112 11aug2019 113 11sep2019 113 11oct2019 114 11oct2019 I need to assign a row number to each distinct Loan_num such as loan_num date1 Row_Num 111 11Jun2019 1 111 11Jul2019 0 112 11Aug2019 1 113 11Sep2019 1 113 11Oct2019 0 114 11Oct2019 1 If the loan_num only appears once, assign a 1. If the loan_num appears twice, assign a 1 to the first one and a 0 to any subsequent loan_num

Use the "little running man" instead, it's next to the </> marked here:

It's not rocket science, and it won't make your head explode. Promised.

To your question:

sort the data by loan_num and whatever you want as an additional sort order, and then, in a subsequent data step, do

by loan_num;
newvar = first.loan_num;

That's all.