DATA Step, Macro, Functions and more

Create a table from multiple tables in some conditions

Reply
Occasional Contributor
Posts: 15

Create a table from multiple tables in some conditions

[ Edited ]

Hello everyone, I have couple questions in SAS…

I want to create a table from multiple tables in some conditions…
Here is my example:

 

Table 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
;

 

Table TWO:

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

date 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
;	

 

Table three: (which I want to create)

 

ID

ID

_CATEGORY

RECOMMEND1

RECOMMEND1_CATEGORY

RECOMMEND2

RECOMMEND2_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

 

For each ID, first of all, observe the events owned by ID in table ONE. Second, according to table TWO, based on the priority, and from the left to the right, to get the events that ID does not hold. Finally, generate two recommended events. And there are two conditions for the recommendation:
1. Generate the same event category as this ID (For Recommend1 filed)

2. For the priority and the order, regardless of the ID's category, generated the second recommendation. But the result is not the same as recommend1. (For Recommend 2 field)

 

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

 

Note:

In fact, I tried to use a 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...

Super Contributor
Super Contributor
Posts: 266

Re: Create a table from multiple tables in some conditions

You don't have any answers because this isn't very well explained.

 

I have no idea how you get to your recommendations. How is G determined as recommendation1 for A1?

 

I have no ideas what you are doing.

Occasional Contributor
Posts: 15

Re: Create a table from multiple tables in some conditions

My apologies for the confusion.

 

Because ID 01 which ID-category is A1, and my first condition is "Output1: The same event category as this ID"

So ID 01 need to be recommended an A1 event, which is item G. (In table one, ID 01 already hold A &B).

 

And the second recommendation is D because of the priority and the sequence in table TWO.

 

Hope you understand what I mean... thanks Smiley Happy

Regular Contributor
Posts: 201

Re: Create a table from multiple tables in some conditions

Interesting problem. You select "G" as RECOMMEND1, because this is the only event in EVENTx_CATEGORY "A1" that is not in dataset ONE. But is "D" selected because of priority 2 or 4?

 

Please post the data as datasteps using datalines statement - copy and pasting those tables creates useless mess in SAS Studio.

Super User
Posts: 13,283

Re: Create a table from multiple tables in some conditions

Posted in reply to error_prone

error_prone wrote:

Interesting problem. You select "G" as RECOMMEND1, because this is the only event in EVENTx_CATEGORY "A1" that is not in dataset ONE. But is "D" selected because of priority 2 or 4?

 

Please post the data as datasteps using datalines statement - copy and pasting those tables creates useless mess in SAS Studio.


And in the Base SAS editors as well.

 

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Occasional Contributor
Posts: 15

Re: Create a table from multiple tables in some conditions

No problem, I corrected the code.
I apologize for any inconvenience. > <
Occasional Contributor
Posts: 15

Re: Create a table from multiple tables in some conditions

Posted in reply to error_prone

Yes, 'D' is selected because of the priority and the sequence.

(In Table TWO, the first priority is 'A' and 'B', but ID 01 has events already. Next, the second priority is 'B' and 'D', that's why 'D' is selected)

 

Thank you for your reply. Smiley Happy

Ask a Question
Discussion stats
  • 6 replies
  • 241 views
  • 0 likes
  • 4 in conversation