SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MohitDamani
Calcite | Level 5

How can i get first occurance basis multiple by variable for eg

for below data i need 1 in new column where ever i have unique combination of id1 & id2

id1id2
100110
100110
100111
100110
100212
100212
100213
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

@MohitDamani wrote:
i need combination of both variables like we do partition by in SQL, for eg row 1,3,5 & 7 should have 1 rest 0


A slight expansion of @PeterClemmensen's code shows that it clearly works:

data have;
input id1 id2;
n = _n_;
datalines;
1001 10
1001 10
1001 11
1001 10
1002 12
1002 12
1002 13
;
run;

proc sort data = have;
	by id1 id2;
run;

data want;
	set have;
	by id1 id2;
	if first.id2 then first_unique = 1;
	else first_unique = 0;
run;

proc print data=want noobs;
run;

Result:

                     first_
  id1    id2    n    unique

 1001     10    1       1  
 1001     10    2       0  
 1001     10    4       0  
 1001     11    3       1  
 1002     12    5       1  
 1002     12    6       0  
 1002     13    7       1  

If you need the original order restored, just sort by n.

View solution in original post

8 REPLIES 8
PeterClemmensen
Tourmaline | Level 20

I think this is what you want, but please post your data in the form of a datastep and describe your desired outcome if not

 

data have;
input id1 id2;
datalines;
1001 10
1001 10
1001 11
1001 10
1002 12
1002 12
1002 13
;

proc sort data = have;
	by id1 id2;
run;

data want;
	set have;
	by id1 id2;
	if first.id2 then first_unique = 1;
	else first_unique = 0;
run;
MohitDamani
Calcite | Level 5
Tried, not working, i need combination of both variables like we do partition by in SQL
MohitDamani
Calcite | Level 5
i need combination of both variables like we do partition by in SQL, for eg row 1,3,5 & 7 should have 1 rest 0
Kurt_Bremser
Super User

@MohitDamani wrote:
i need combination of both variables like we do partition by in SQL, for eg row 1,3,5 & 7 should have 1 rest 0


A slight expansion of @PeterClemmensen's code shows that it clearly works:

data have;
input id1 id2;
n = _n_;
datalines;
1001 10
1001 10
1001 11
1001 10
1002 12
1002 12
1002 13
;
run;

proc sort data = have;
	by id1 id2;
run;

data want;
	set have;
	by id1 id2;
	if first.id2 then first_unique = 1;
	else first_unique = 0;
run;

proc print data=want noobs;
run;

Result:

                     first_
  id1    id2    n    unique

 1001     10    1       1  
 1001     10    2       0  
 1001     10    4       0  
 1001     11    3       1  
 1002     12    5       1  
 1002     12    6       0  
 1002     13    7       1  

If you need the original order restored, just sort by n.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Post test data in the form of a datastep!!

 

/* assumes sorted */

data want;
  set have;
  by id1 id2;
  if first.id2 then new_var=1;
run;
TarunKumar
Pyrite | Level 9

data have;
input id1 id2;
datalines;
1001 10
1001 10
1001 11
1001 10
1002 12
1002 12
1002 13
;
RUN;

 

proc sort data = have;
by id1 id2;
run;

 

data want;
set have;
by id1 id2;
if first.id1 then first_unique = 1;
else first_unique = 0;
run;

 

OR 

 

if yo want to extract the unique data then pls use below code ;

 


proc sort data = have OUT= WANT NODUP;BY ID1;

run;

Jagadishkatam
Amethyst | Level 16

I am not sure of your expected output, you want the unique records per id1 and id2 without the duplicates I mean if there are a combination of dulicates on id1 and id2 then exclude them from flagging

 


data want;
set have;
by id1 id2;
if first.id2 and last.id2 then flag=1;
else flag=0;
run;
Thanks,
Jag

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 74964 views
  • 1 like
  • 6 in conversation