Filling a table field by field using a lookup dataset

[Jazzman]

Hello,

I'm creating a new table using a data step where I need to fill the blanks with values from another table following a non-trivial mapping logic (i. e. I have to use a singular rule for every single combination of line and column).

This is the structure of the new table:

DATA newtable;
input linename $;
DATALINES;
line1
line2
line3
;
col1 = .;
col2 = .;
col3 = .;
RUN;

My lookup table looks like this:

DATA lookuptable;
input level1 level2 value;
DATALINES;
1 1 10
1 2 10
1 3 20
2 1 20
2 2 10
2 3 45
3 1 41
3 2 12
3 3 14
;
RUN;

The task is to be able to fill NEWTABLE with values from LOOKUPTABLE using values of LINENAME and the name of the variable (i. e. col1 to col3) to select which cell to fill and values of LEVEL1 and LEVEL2 to choose which value to use. I can't seem to find a reasonable way of doing this with SAS Base. Any suggestions?

[Tom]

I do not see any relationship at all between those two tables.  The first one has variables named LINENAME and empty variables COL1 to COL3.  The second has columns named LEVEL1 LEVEL2 and VALUE.  How are they related?

You seem to be mentioning some type or data driven coding?  But again I don't see anything that would link the two tables. The variable LINENAME has values like 'line1'.  What would that link to? 

[Jazzman]

I'm sorry if this example was too abstract. The missing link is just a mapping table that would look something like

target_line target_column source_level1 source_level2
line1       col1          3             1
line1       col2          3             2

and so on so that the first table can be filled with values from the second one after this set of rules.

[Tom]

So perhaps you just need to join the tables together.  Is there any reason to worry about performance?

Show an actual worked example.  What values do the metadata tables have.  What values does the source data have and what values do you want it to have after the operation is complete.  Where did the new values come from?  Either show how to select those values or explain the logic clearly.

[Jazzman]

Performance is not an issue, neither of the tables is very big.

The source table is a data source that contains count data from a cross tabulation (each cell contains the number of individuals that combine certain expressions of two variables).

The table I'm trying to create reorders the data after a different logic as an actual cross table (variable 1 in lines, variable 2 in columns) for a custom report that I'm going to generate with PROC PRINT.

How would this kind of join look? This is beyond what I usually do with table joins.

[Tom]

Still clear as mud.  Sounds like you probably want to transpose the first table from your example so that the column name moves out of the metadata of the table into a variable that can be used to join with your other tables.

DATA newtable;
length line_name $32 column_name $32 value 8;
do line_name='line1','line2','line3';
do column_name='col1','col2','col3';
  value=.;
   output;
end;
end;
run;

You can then use PROC TRANSPOSE to convert it back into the regular form.  Or use PROC REPORT instead of PROC PRINT to make your report.

[Jazzman]

No, you missunderstood. I'd like to generate a SAS data file with cross table structure from cross tabulation Information in long format if you will.