topic Re: Retrieve data from multiple datasets in some conditions in SAS Programming

Retrieve data from multiple datasets in some conditions

wenzli25 — Mon, 11 Dec 2017 11:42:36 GMT

(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 > <

Re: Retrieve data from multiple datasets in some conditions

novinosrin — Sat, 09 Dec 2017 19:17:48 GMT

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.

Re: Retrieve data from multiple datasets in some conditions

wenzli25 — Mon, 11 Dec 2017 03:24:19 GMT

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...

Re: Retrieve data from multiple datasets in some conditions

novinosrin — Mon, 11 Dec 2017 03:44:17 GMT

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

Re: Retrieve data from multiple datasets in some conditions

wenzli25 — Mon, 11 Dec 2017 11:46:06 GMT

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 !!!

Re: Retrieve data from multiple datasets in some conditions

novinosrin — Mon, 11 Dec 2017 19:56:14 GMT

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

Re: Retrieve data from multiple datasets in some conditions

wenzli25 — Tue, 12 Dec 2017 06:13:55 GMT

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. ^_^