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 |
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 |
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(nv); 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.
@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.
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
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.
@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?
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 | ? |
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.