calling a value using row and column.

[Decay2020]

Hi, I have table 'have' and first 2 columns of table 'want'. 

I want to get the corresponding values with respect to each var1 and var2. Please help if someone has the solution.

Table have:

	A	B	C
A	1	2	3
B	4	5	6
C	7	8	9

Table Want:

VAR 1	VAR 2	VALUE
A	A	1
A	B	2
A	C	3
B	A	4
B	B	5
B	C	6
C	A	7
C	B	8
C	C	9

[Decay2020]

Hi, I have table 'have' and first 2 columns of table 'want'. 

I want to get the corresponding values with respect to each var1 and var2. Please help if someone has the solution.

Table have:

	A	B	C
A	1	2	3
B	4	5	6
C	7	8	9

Table Want:

VAR 1	VAR 2	VALUE
A	A	1
A	B	2
A	C	3
B	A	4
B	B	5
B	C	6
C	A	7
C	B	8
C	C	9

[ballardw]

Duplicate posts combined.

You "have" description is incomplete as a SAS data set must have a name for every variable, i.e. "column".

This is one way:

data have;
input row $ A	B	C;
datalines;
A	1	2	3
B	4	5	6
C	7	8	9
;

data want;
   set have;
   array v (*) A B C;
   do i=1 to dim(n v);
      var1= row;
      var2 = vname(v[i]);
      value = v[i];
      output;
   end;
   keep var1 var2 value;
run;

For this approach to work all of the variables A B C, or whatever the real ones may be must be of the same type, either all numeric or all character If they are mixed then you have a more complicated problem.

If you have any other variables involved you may need to show how you expect their values to be handled as well.

[Decay2020]

I am getting this error:
ERROR: The DIM, LBOUND, and HBOUND functions require an array name for the first argument.

Also, we have var1 and var2 which are the combinations from the table 'Have'. We are more interested in getting the 'values' with respect to var1 and var2 combination.
And, a correction in the problem, just think if there are hundreds of columns and rows, not just 3 (namely =A,B and C).

[Reeza]

Number of rows doesn't matter to the solution.
For the increase in variables you can list just the first and last variables, you don't need to list all.
This would use all variables between A and C.

array v (*) A -- C;

Change @ballardw code to : dim(v) from dim(n).

Small typo.
The links in my first answer provide more details on these methods and they both scale.

[ballardw]

---

@Reeza wrote:
Number of rows doesn't matter to the solution.
For the increase in variables you can list just the first and last variables, you don't need to list all.
This would use all variables between A and C.

array v (*) A -- C;

Change @ballardw code to : dim(v) from dim(n).

Small typo.
The links in my first answer provide more details on these methods and they both scale.

---

Good catch. I tested the code with Array N and then started changing it to avoid the "name is the same as function" messages and missed the change on the Do statement.

[Reeza]

I almost typed that out as well 😄

[Reeza]

TRANSPOSE.

Transposing data tutorials:
Wide to Long:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/

https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/

proc transpose data=have out=want;
by ID;
var A B C;
run;

---

@Decay2020 wrote:

Hi, I have table 'have' and first 2 columns of table 'want'. 

I want to get the corresponding values with respect to each var1 and var2. Please help if someone has the solution.

Table have:

	A	B	C
A	1	2	3
B	4	5	6
C	7	8	9

Table Want:

VAR 1	VAR 2	VALUE
A	A	1
A	B	2
A	C	3
B	A	4
B	B	5
B	C	6
C	A	7
C	B	8
C	C	9

---

[Decay2020]

Yes, you are partially right. As I tried to give a simple example to get an idea about the solution, but the reality is completely different. Just imagine there are hundreds of columns instead of just 3 (namely A, B , C)  in table 'Have' and we don't know if we have all the combination of those in var1 and var2 in the table 'want'. Now we are interested in getting only the values with respect to those combinations that are present in Table 'want'. Let me know if I was able to explain the things further. 

[ballardw]

---

@Decay2020 wrote:

Yes, you are partially right. As I tried to give a simple example to get an idea about the solution, but the reality is completely different. Just imagine there are hundreds of columns instead of just 3 (namely A, B , C)  in table 'Have' and we don't know if we have all the combination of those in var1 and var2 in the table 'want'. Now we are interested in getting only the values with respect to those combinations that are present in Table 'want'. Let me know if I was able to explain the things further. 

---

"All possible combinations" is a somewhat different kettle of fish. And hundreds of columns (really time to use variables if the data is in SAS) translates into extremely large numbers of combinations quickly. If you have 100 variables and each has only 3 values you are looking for 3**100 combinations, or about 5.1537752E47 combinations. That is 5 followed by 47 more digits. 200 variables pushes that to  2.65E95 combinations. Remember this example is only using 3 values per variable. If you have stuff like account numbers, dates or similar that have more possible values then your combinations goes up even quicker.

Which "var1" and "var2" are referring to? After a data set has been transposed as shown? Or specific existing variables in the data?

[Decay2020]

Thank you for your quick turnaround.

That is the reason we don't have that much combinations. we are trying get the value for only the subset. Also, i just mould the problem which makes much more sense. 

This is what we have. we need to populate column3 in table 2.

	Person A	Person B	Person C
Person A	1	2	3
Person B	6	7	8
Person C	11	12	13

year 1	year 2	Value
Person A	Person B	?
Person B	Person C	?
Person C	Person B	?
Person B	Person C	?
Person C	Person A	?

[ballardw]

---

@Decay2020 wrote:

Thank you for your quick turnaround.

That is the reason we don't have that much combinations. we are trying get the value for only the subset. Also, i just mould the problem which makes much more sense. 

This is what we have. we need to populate column3 in table 2.

	Person A	Person B	Person C
Person A	1	2	3
Person B	6	7	8
Person C	11	12	13

year 1	year 2	Value
Person A	Person B	?
Person B	Person C	?
Person C	Person B	?
Person B	Person C	?
Person C	Person A	?

---

Now you have introduced the concept of time by referencing Year without any source in the "start" data. Which would bring up where does year come from, how many "years" are there and what actual data sets are involved.

[Decay2020]

Please treat year1 as the previous year and year 2 as the present for your reference. But, concept of time is not applicable here. you can treat year1 and year2 as any character variable.
Again, we are interested in calling the value where the combination is. like for example personA and personB should yield a value of 2.
I will try to work on making the best sample of my problem too. Meanwhile thanks for your help.

[Reeza · Accepted solution]

1. Use @ballardw or my solution to transpose your data.
2. Use a left join to merge the smaller table with your big table.

If you'd like a full overview of all the lookup methods and the different advantages read this paper please:
https://support.sas.com/resources/papers/proceedings/pdfs/sgf2008/095-2008.pdf

[Decay2020]

Thank you both for helping out. i have got the idea. i will definitely use the solution and try to mould it to get to the desired output.