BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vicky07
Quartz | Level 8

Hello everyone,

 

I have a scenario where I need to find whether the same "reward_program_id1" OR "reward_program_id2" is used in multiple product_id. In the below data product_id is unique however, reward_program_id1 & reward_program_id2 digits can be used interchangeable to purchase product. 

 

For example,  product_id 89067 is purchased using reward_program_id2 = 567694(row #1)  and the same digits are used as reward_program_id1 to purchase product_id 90873 (row #4).  Likewise,  product_id 34340 is purchased using reward_program_id1 = 429008(row #3)  and the same digits are used as reward_program_id2 to purchase product_id 10983(row #8).

 

In the output I am trying to add a column "Multi_program_id" Y/N if the same reward ID is used in more than one product. I hope this makes sense, any help is appreciated!

Thanks in advance. 

 

Data have;
input product_id product_delivered $ reward_program_id1 reward_program_id2 ;
infile datalines delimiter=',';
datalines;
89067,Y,223456,567694
58394,N,343412,
34340,Y,429008,412948
90873,N,567694,293832
91028,N,389292,902221
38034,N,459303,343033
76281,N,828292,282929
10983,Y,909033,429008
18738,N,929292,
17930,Y,343412,982932
29383,N,898903,
11283,Y,976409,829737
98203,N,839234,928302
12930,N,918833,303033
78023,Y,123233,412948
;
run;

 

Output:

 

Product_id product_delivered reward_program_id1 reward_program_id2 Multi_program_id
89067 Y 223456 567694 Y
58394 N 343412 . Y
34340 Y 429008 412948 Y
90873 N 567694 293832 Y
91028 N 389292 902221 N
38034 N 459303 343033 N
76281 N 828292 282929 N
10983 Y 909033 429008 Y
18738 N 929292 . N
17930 Y 343412 982932 Y
29383 N 898903 . N
11283 Y 976409 829737 N
98203 N 839234 928302 N
12930 N 918833 303033 N
78023 Y 123233 412948 Y

 

1 ACCEPTED SOLUTION

Accepted Solutions
ErikLund_Jensen
Rhodochrosite | Level 12

Hi @vicky07 

This an interesting problem, because the caunt of same reward-id on a product is taken from one or other both of the columns reward_program_id1 and reward_program_id2. So it ended up a bit more complicated than I thought from start. But I think it works.

 

Data have;
input product_id product_delivered $ reward_program_id1 reward_program_id2 ;
infile datalines delimiter=',';
datalines;
89067,Y,223456,567694
58394,N,343412,
34340,Y,429008,412948
90873,N,567694,293832
91028,N,389292,902221
38034,N,459303,343033
76281,N,828292,282929
10983,Y,909033,429008
18738,N,929292,
17930,Y,343412,982932
29383,N,898903,
11283,Y,976409,829737
98203,N,839234,928302
12930,N,918833,303033
78023,Y,123233,412948
;
run;


* Collapse reward_program_id1 and reward_program_id2 til one column without misings;
* order on reward_program_id to use in following data step;
proc sql;
	create table rewardproduct as
		select distinct 
			product_id, 
			reward_program_id
		from (
		 	select 
				product_id, 
				reward_program_id1 as reward_program_id
			from have
			where reward_program_id1 ne .
			union
	 		select 
				product_id, 
				reward_program_id2 as reward_program_id
			from have
			where reward_program_id2 ne .
		)
		order by 
			reward_program_id, 
			product_id;
quit;

* Count products per program_id and set code for multiple products pr. reward_program_id;
data multi;
	set rewardproduct;
	by reward_program_id;
	if (first.reward_program_id and last.reward_program_id) then multi = 0;
	else multi = 1;
run;

* Set highest value of products pr. reward_program_id as multivalue on product;
proc sql;
	create table multi2 as
		select distinct 
			product_id, 
			ifc(max(multi)=1,'Y','N') as Multi_Program_id
		from multi
		group by product_id;
quit;

* Join to get final result;
proc sql;
	create table want as 
		select distinct 
			a.product_id, 
			a.product_delivered, 
			a.reward_program_id1, 
			a.reward_program_id2, 
			b.Multi_Program_id
		from have as a
		left join multi2 as b
		on a.product_id = b.product_id;
quit;

View solution in original post

3 REPLIES 3
ErikLund_Jensen
Rhodochrosite | Level 12

Hi @vicky07 

This an interesting problem, because the caunt of same reward-id on a product is taken from one or other both of the columns reward_program_id1 and reward_program_id2. So it ended up a bit more complicated than I thought from start. But I think it works.

 

Data have;
input product_id product_delivered $ reward_program_id1 reward_program_id2 ;
infile datalines delimiter=',';
datalines;
89067,Y,223456,567694
58394,N,343412,
34340,Y,429008,412948
90873,N,567694,293832
91028,N,389292,902221
38034,N,459303,343033
76281,N,828292,282929
10983,Y,909033,429008
18738,N,929292,
17930,Y,343412,982932
29383,N,898903,
11283,Y,976409,829737
98203,N,839234,928302
12930,N,918833,303033
78023,Y,123233,412948
;
run;


* Collapse reward_program_id1 and reward_program_id2 til one column without misings;
* order on reward_program_id to use in following data step;
proc sql;
	create table rewardproduct as
		select distinct 
			product_id, 
			reward_program_id
		from (
		 	select 
				product_id, 
				reward_program_id1 as reward_program_id
			from have
			where reward_program_id1 ne .
			union
	 		select 
				product_id, 
				reward_program_id2 as reward_program_id
			from have
			where reward_program_id2 ne .
		)
		order by 
			reward_program_id, 
			product_id;
quit;

* Count products per program_id and set code for multiple products pr. reward_program_id;
data multi;
	set rewardproduct;
	by reward_program_id;
	if (first.reward_program_id and last.reward_program_id) then multi = 0;
	else multi = 1;
run;

* Set highest value of products pr. reward_program_id as multivalue on product;
proc sql;
	create table multi2 as
		select distinct 
			product_id, 
			ifc(max(multi)=1,'Y','N') as Multi_Program_id
		from multi
		group by product_id;
quit;

* Join to get final result;
proc sql;
	create table want as 
		select distinct 
			a.product_id, 
			a.product_delivered, 
			a.reward_program_id1, 
			a.reward_program_id2, 
			b.Multi_Program_id
		from have as a
		left join multi2 as b
		on a.product_id = b.product_id;
quit;
tarheel13
Rhodochrosite | Level 12

I was going to post a similar approach. Is there another way to make the multi flag other than first and last variables? I didn't post my solution because I was not finished but I wanted to get a count of reward by product_id and then if it was ge 2 then it was in more than one product. Would something close to that work? 

vicky07
Quartz | Level 8
It worked. Thank you very much!!

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 545 views
  • 1 like
  • 3 in conversation