BookmarkSubscribeRSS Feed
wenzli25
Calcite | Level 5

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

6 REPLIES 6
HB
Barite | Level 11 HB
Barite | Level 11

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.

wenzli25
Calcite | Level 5

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 🙂

error_prone
Barite | Level 11

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.

ballardw
Super User

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

wenzli25
Calcite | Level 5
No problem, I corrected the code.
I apologize for any inconvenience. > <
wenzli25
Calcite | Level 5

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

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
  • 1207 views
  • 0 likes
  • 4 in conversation