BookmarkSubscribeRSS Feed
wenzli25
Calcite | Level 5

(The topic I posted was missing, I don't know why..., so I reposted the topic. T____T)

 

Hello everyone, I have couple questions in SAS…I want to retrieve data from multiple datasets in some conditions.

In brief, there are two datasets. The first one is the events owned by customers, another one is the order of

priority for the events. What I want to do is to recommend two events that not yet owned by the customer.
However, there are two conditions, the first one is the category of recommended event is the same as the

category of customer. The second one is based on the priority and the order, recommend the event not owned

by the customer, and different to the first recommended event.

 

I tried to use cross join on two tables, then, used the loop function to match the criteria.

But I'm wondering if there is any more efficient way, or my way is wrong...

 

The following is a simple example.

 

Dataset one

Description: ID only belongs one category, but one ID has multiple events, and event belongs one event category.

data one;
   input ID $ ID_CATEGORY $ EVENT $ EVENT_CATEGORY $;
   datalines;
01 A1 A A1
01 A1 B A1
01 A1 C B1
02 B1 A A1
02 B1 D B1
03 B1 E B1
04 A1 A A1
;

Dataset two

Description: Based on the priority, each row has two events, and each belongs their categories.

data two;
   input PRIORITY EVENT1 $ EVENT1_CATEGORY $ EVENT2 $ EVENT2_CATEGORY $;
   datalines;
1 A A1 B A1
2 B A1 D B1
3 C B1 A A1
4 D B1 E B1
5 E B1 G A1
;	

Dataset three (which I want to create)

ID

ID

_CATEGORY

ITEM1

ITEM1_CATEGORY

ITEM2

ITEM2_CATEGORY

01

A1

G

A1

D

B1

02

B1

C

B1

B

A1

03

B1

D

B1

A

A1

04

A1

B

A1

D

B1

 Explanation:

For ID 01, ID's category is A1 (show in dataset one), and the recommended item1 is 'G', because 'G' is the

first event in category 'A1' (show in dataset two).

Then, the recommended item2 is 'D', because of the priority and the order. (Priority1 is 'A' and 'B' item in dataset two,

but ID 01 has already held two events in dataset one. Priority2 is 'B' and 'D', so 'D' is selected.)

 

Could you help me to figure out this question how to solve... or any recommended functions?? Thanks > <

 

6 REPLIES 6
novinosrin
Tourmaline | Level 20







wenzli25 wrote:

(The topic I posted was missing, I don't know why..., so I reposted the topic. T____T)

 

 

 Hi , I believe you may be able to retrieve your lost topic and I think your best is to to approach @ChrisHemedinger  perhaps on a weekday requesting to retrieve that thread. Had the question been answered, would only make it redundant. Having 6 hours past since your re posting with 80+ views and no responses yet makes me wonder retrieving your lost thread would help.

 

 

 

wenzli25
Calcite | Level 5

Hi novinosrin,

Thank you for your help. My old topic has been recovered, but I haven't got the solution. Do not know if I didn't explain my question well... I'll keep waiting and trying...

novinosrin
Tourmaline | Level 20

wenzli25 wrote:

"Do not know if I didn't explain my question well... I'll keep waiting and trying..."


 

I appreciate that sincere attitude. I didn't read your question at length and on the surface seemed not so clear. I do not have SAS software at home. I can only look into it tomorrow when I go to college. If your question isn't answered by then, I'll give it a shot. Have a good night

wenzli25
Calcite | Level 5

Thank you ^ _ ^
I revised the description of the content, I hope this easier to understand.
Thank you very much for your reply and help. Have a good night !!!

novinosrin
Tourmaline | Level 20

Hey Good afternoon, Sorry for being late. I slept off and train schedules all got delayed. I do need some clarification on your explanation though:

 

 Explanation:

For ID 01, ID's category is A1 (show in dataset one), and the recommended item1 is 'G', because 'G' is the

first event in category 'A1' (show in dataset two). A. What look up key from dataset one fetches 'G'  which is in EVENT2 of dataset two? I am assuming it is ID_category of one<--> Event2_category? and What look up key from dataset one fetches 'D' from EVENT2   dataset two? I am assuming it is ID_category of one<--> Event2_category

Then, the recommended item2 is 'D', because of the priority and the order. (Priority1 is 'A' and 'B' item in dataset two,

but ID 01 has already held two events in dataset one. Priority2 is 'B' and 'D', so 'D' is selected.) and What look up key from dataset one fetches 'D' from EVENT2   dataset two? I am assuming it is ID_category of one<--> 

 

In essence, I need you to explain me the look up operation of the logic with "keys" and priority clearly like what fetches what and why(priority).

 

It is 2 pm here in Chicago. I should be here till 7 pm. If I could get the clarity, I should be able to help. 

 

Dataset three (which I want to create)

ID

ID

_CATEGORY

ITEM1

ITEM1_CATEGORY

ITEM2

ITEM2_CATEGORY

01

A1

G

A1

D

B1

02

B1

C

B1

B

A1

03

B1

D

B1

A

A1

04

A1

B

A1

D

B1

 

wenzli25
Calcite | Level 5

Hi novinosrin,


Sorry for the late reply, because we're in different time zones.
The format of my original data is like dataset two, but I think that data transposition can be done to facilitate data processing and understanding. The converted format is as follows:

data two;
   input PRIORITY EVENT $ EVENT_CATEGORY $ ;
   datalines;
1 A A1 
1 B A1
2 B A1 
2 D B1
3 C B1 
3 A A1
4 D B1 
4 E B1
5 E B1 
5 G A1
;	


 Explanation:

For ID 01, ID's category is A1 (show in dataset one), and the recommended item1 is 'G', because 'G' is the

first event in category 'A1' (show in dataset two). A. What look up key from dataset one fetches 'G'  which is in EVENT2 of dataset two? I am assuming it is ID_category of one<--> Event2_category?

 

For recommended event1

-> ID_category of dataset one = Event_category of dataset two

-> and ID_event of one ^= Event of two

-> The order of priority is from top to bottom of dataset two

 

Then, the recommended item2 is 'D', because of the priority and the order. (Priority1 is 'A' and 'B' item in dataset two,

but ID 01 has already held two events in dataset one. Priority2 is 'B' and 'D', so 'D' is selected.) and What look up key from dataset one fetches 'D' from EVENT2   dataset two? I am assuming it is ID_category of one<--> 

 

For recommended event2

-> ID_event of dataset one ^= Event of dataset two

-> recommended event2 ^= recommended event1

-> The order of priority is from top to bottom of dataset two

 

In essence, I need you to explain me the look up operation of the logic with "keys" and priority clearly like what fetches what and why(priority).

 




Thank you for your help, I think I have some ideas, I'll continue to try some methods. ^_^

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

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
  • 6 replies
  • 1001 views
  • 0 likes
  • 2 in conversation