need advice on making a dynamic algorithm

[S420L]

To make a long story short I've had to learn SAS from knowing nothing in the past month to do a modeling project of sorts for my work. I have a background in SQL and got almost all the way done using PROC SQL but this last step I can't figure out how to do with either PROC SQL, call SYMPUT, or data step arrays--so I'm looking for wisdom on how to approach this problem different. You'll have to follow my logic for a little bit so for anyone who actually reads this through I appreciate the patience. 

I attached PDFs that are pictures of intermediate tables in my project:

• Table 1 has a list of variables each with a 1-6 "rating", and to the right 4 "Rules" labeled R1,R3,R5,R7 (these could be any amount between R1 and R30 in ascending order). 
• Table 2 has 7 columns: the first column contains the values R1,R3,R5,R7 (these will always match up with the values in Table 1) and the rest of the columns are the working parts of the 4 Rules that each need to be compared with the 1-6 rating in Table 1 to produce a conclusion. There will always be 7 columns in this table but there might be any number of observations depending on how many "Rules" might apply.

This whole problem of having to compare each individual number to each rating is what's stumping me. Each rule has 6 parts to it (_35-->_40 in Table 2) so there need to be 24 comparisons made and hence some way to store each number from this table in a data structure so they can be compared in an iterative fashion with things in Table 1. Most of what I've done so far as involved either single variables or PROC SQL so I'm not sure of how to approach the problem of bringing two different sized tables together with variable names that can be looped through.

So far the farthest I've come is creating a list of Macro variables from each column in Table 2 then trying to assign them to data step arrays as a way to add 24 new variables to Table 1. 

This would take a column from Table 2 and assign it to listName1-->listNameN:

        %macro toMacroList(listVar,listName);
        data _null_;
        if eof then call symputx('num_or',_n_-1);
        set CondensedOverrides4 end=eof ;
        call symputx(cats("&listName",_n_),&listVar,"G");
        run; 
        %mend;

This would take the macro list and assign listNamej to array{j}:

        %macro toDataArray(array,listName);
        %do j = 1 %to &num_or;
        &array[&j.] = "&&&listName&j";
        %end;
        run;
        %mend;

I've had all sorts of problems trying to make this way work because calling multiple %toDataArray statements in the data step seems to break my program. 

I would appreciate if someone wants to try to understand this problem I'm trying to solve and tell me if I'm approaching it totally the wrong way or not, and maybe point me towards resources to learn stuff having to do with this better. I have a good idea how I would do this in like C or Java but SAS seems really different when it comes to data structures playing nice together and I'm struggling making much progress past this point.

Thanks!

[Reeza]

Show us a text sample (not typing out data) and what you want as output. Your text isn't really clear on the expected output at this point in time. 

I suspect a FORMAT may work or a different type of join. 

---

@S420L wrote:

To make a long story short I've had to learn SAS from knowing nothing in the past month to do a modeling project of sorts for my work. I have a background in SQL and got almost all the way done using PROC SQL but this last step I can't figure out how to do with either PROC SQL, call SYMPUT, or data step arrays--so I'm looking for wisdom on how to approach this problem different. You'll have to follow my logic for a little bit so for anyone who actually reads this through I appreciate the patience. 

I attached PDFs that are pictures of intermediate tables in my project:

• Table 1 has a list of variables each with a 1-6 "rating", and to the right 4 "Rules" labeled R1,R3,R5,R7 (these could be any amount between R1 and R30 in ascending order). 
• Table 2 has 7 columns: the first column contains the values R1,R3,R5,R7 (these will always match up with the values in Table 1) and the rest of the columns are the working parts of the 4 Rules that each need to be compared with the 1-6 rating in Table 1 to produce a conclusion. There will always be 7 columns in this table but there might be any number of observations depending on how many "Rules" might apply.

This whole problem of having to compare each individual number to each rating is what's stumping me. Each rule has 6 parts to it (_35-->_40 in Table 2) so there need to be 24 comparisons made and hence some way to store each number from this table in a data structure so they can be compared in an iterative fashion with things in Table 1. Most of what I've done so far as involved either single variables or PROC SQL so I'm not sure of how to approach the problem of bringing two different sized tables together with variable names that can be looped through.

So far the farthest I've come is creating a list of Macro variables from each column in Table 2 then trying to assign them to data step arrays as a way to add 24 new variables to Table 1. 

This would take a column from Table 2 and assign it to listName1-->listNameN:

        %macro toMacroList(listVar,listName);
        data _null_;
        if eof then call symputx('num_or',_n_-1);
        set CondensedOverrides4 end=eof ;
        call symputx(cats("&listName",_n_),&listVar,"G");
        run; 
        %mend;

This would take the macro list and assign listNamej to array{j}:

        %macro toDataArray(array,listName);
        %do j = 1 %to &num_or;
        &array[&j.] = "&&&listName&j";
        %end;
        run;
        %mend;

I've had all sorts of problems trying to make this way work because calling multiple %toDataArray statements in the data step seems to break my program. 

I would appreciate if someone wants to try to understand this problem I'm trying to solve and tell me if I'm approaching it totally the wrong way or not, and maybe point me towards resources to learn stuff having to do with this better. I have a good idea how I would do this in like C or Java but SAS seems really different when it comes to data structures playing nice together and I'm struggling making much progress past this point.

Thanks!

---

[S420L]

I sketched out the logic for an example with 6 observations and one rule:

• if R1="V" and is 1 or 2 
• and if another R1="O" and is 4 or 5
• and the above 2 things are true 1 time
• new rating=3

This logic is being performed on a table brought in from an Excel model which is why the logic isn't set up in a way conducive to SAS. In this example we have a table with 6 observations and a table with 1 observation. In the example I originally posted we have a table with 11 observations and a table with 4. It's less the logic and output I want advice on as it is how to compare tables that are fundamentally different sizes in SAS.