(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 > <
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.
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...
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
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 !!!
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 |
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. ^_^
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.