topic Re: mapping different values on basis of row and column in SAS Programming

calling a value using row and column.

Decay2020 — Thu, 15 Jul 2021 15:33:59 GMT

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

mapping different values on basis of row and column

Decay2020 — Thu, 15 Jul 2021 15:39:41 GMT

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

Re: mapping different values on basis of row and column

ballardw — Thu, 15 Jul 2021 16:57:45 GMT

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.

Re: calling a value using row and column.

Reeza — Thu, 15 Jul 2021 15:49:07 GMT

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

Re: calling a value using row and column.

Reeza — Thu, 15 Jul 2021 15:54:12 GMT

FYI more common terms for this task are transpose or pivot. If you search with those terms in any language you'll find an answer.

Re: calling a value using row and column.

Decay2020 — Thu, 15 Jul 2021 16:34:28 GMT

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.

Re: mapping different values on basis of row and column

Decay2020 — Thu, 15 Jul 2021 16:41:20 GMT

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

Re: mapping different values on basis of row and column

Reeza — Thu, 15 Jul 2021 16:44:25 GMT

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.

Re: calling a value using row and column.

ballardw — Thu, 15 Jul 2021 16:45:39 GMT

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

Re: calling a value using row and column.

Reeza — Thu, 15 Jul 2021 16:51:58 GMT

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

Doesn't matter, it doesn't change my solution.

The number of rows are irrelevant and the number of columns can be easily referenced using shortcut lists.

Here is a reference that illustrates how to refer to variables and datasets in a short cut list:
https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

@Decay2020 wrote:

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.

Then why work with the whole data set at all? Generate your list from your WANT data set and merge it in. Unless there's something else you're not explaining this looks like a standard look up problem. Another approach that make more sense to the way you seem to be thinking is to load it into a two dimensional temporary array. If you provide a full example of input and expected output I'll take a stab at coding that. Please post your data as a data step following the instructions below.

Make sure to include both your exact input and the matching expected output. If you cannot provide real data, make fake data that's representative of your problem.

Instructions on how to provide sample data is here:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Re: mapping different values on basis of row and column

ballardw — Thu, 15 Jul 2021 16:59:04 GMT

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

Re: calling a value using row and column.

Decay2020 — Thu, 15 Jul 2021 17:02:38 GMT

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	?

Re: mapping different values on basis of row and column

Reeza — Thu, 15 Jul 2021 17:03:53 GMT

I almost typed that out as well 😄

Re: calling a value using row and column.

ballardw — Thu, 15 Jul 2021 17:07:53 GMT

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

Re: calling a value using row and column.

Reeza — Thu, 15 Jul 2021 17:18:13 GMT

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

Re: calling a value using row and column.

Decay2020 — Thu, 15 Jul 2021 17:18:35 GMT

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.

Re: calling a value using row and column.

Decay2020 — Thu, 15 Jul 2021 17:24:12 GMT

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.