Hi community,
I would like to know how to transpose values from a table that I have to table that I want as it is described below:
What I have | ||||
ID | Marketed | Value1 | Value2 | Value3 |
F1 | A | 13 | 207 | 1 |
F1 | A | 13 | 207 | 1 |
F1 | A | 13 | 207 | 1 |
F1 | B | 18 | 205 | 3 |
F1 | B | 18 | 205 | 3 |
F1 | B | 18 | 205 | 3 |
F1 | B | 18 | 205 | 3 |
F1 | C | 15 | 208 | 4 |
F1 | C | 15 | 208 | 4 |
F2 | A | 19 | 220 | 8 |
F2 | A | 19 | 220 | 8 |
F2 | B | 14 | 210 | 6 |
F2 | C | 12 | 212 | 5 |
F3 | A | 15 | 230 | 10 |
So, my goal is this next table below (because in the end I will transform each ID in a single line:
What I want | |||||||||
ID | Value1A | Value1B | Value1C | Value2A | Value2B | Value2C | Value3A | Value3B | Value3C |
F1 | 13 | 18 | 15 | 207 | 205 | 208 | 1 | 3 | 4 |
F1 | |||||||||
F1 | |||||||||
F1 | |||||||||
F1 | |||||||||
F1 | |||||||||
F1 | |||||||||
F1 | |||||||||
F1 | |||||||||
F2 | 19 | 14 | 12 | 220 | 210 | 212 | 8 | 6 | 5 |
F2 | |||||||||
F2 | |||||||||
F2 | |||||||||
F3 | 15 | 230 | 10 |
Thanks in advance community,
Daniel
Another solution:
data temp;
set have; by id marketed notsorted;
array v value:;
if first.marketed;
length var $32;
do i = 1 to dim(v);
var = cats(vname(v{i}), marketed);
val = v{i};
output;
end;
keep id marketed var val;
run;
proc sort data=temp; by id var; run;
proc transpose data=temp out=want(drop=_name_);
by id;
var val;
id var;
run;
proc print data=want noobs; run;
Well, yes you could do this transpose, but it will make all subsequent programming much harder compared to not transposing. What is the reason you need the data transposed like this? What are you going to do with this transposed data set?
Especially why the many rows of missing values?
But you didn't answer my questions.
Specifically, I asked:
What is the reason you need the data transposed like this? What are you going to do with this transposed data set?
@Moraes86 Do you need a table or a report? There will be multiple ways to get what you want. Just show us the desired end result and be clear if you need this as a table or as a report.
data have;
input ID $ Marketed $ Value1 Value2 Value3;
datalines;
F1 A 13 207 1
F1 A 13 207 1
F1 A 13 207 1
F1 B 18 205 3
F1 B 18 205 3
F1 B 18 205 3
F1 B 18 205 3
F1 C 15 208 4
F1 C 15 208 4
F2 A 19 220 8
F2 A 19 220 8
F2 B 14 210 6
F2 C 12 212 5
F3 A 15 230 10
;
proc sql;
select
id,
marketed,
sum(value1) as sum_value1,
sum(value2) as sum_value2,
sum(value3) as sum_value3
from have
group by
id,
marketed
;
quit;
options missing=' ';
proc tabulate data=have;
class id Marketed;
var value1 value2 value3;
keylabel sum=' ';
table
id,
Marketed*
(value1 value2 value3)*f=16.
;
run;
Another solution:
data temp;
set have; by id marketed notsorted;
array v value:;
if first.marketed;
length var $32;
do i = 1 to dim(v);
var = cats(vname(v{i}), marketed);
val = v{i};
output;
end;
keep id marketed var val;
run;
proc sort data=temp; by id var; run;
proc transpose data=temp out=want(drop=_name_);
by id;
var val;
id var;
run;
proc print data=want noobs; run;
data have; input ID $ Marketed $ Value1 Value2 Value3; datalines; F1 A 13 207 1 F1 A 13 207 1 F1 A 13 207 1 F1 B 18 205 3 F1 B 18 205 3 F1 B 18 205 3 F1 B 18 205 3 F1 C 15 208 4 F1 C 15 208 4 F2 A 19 220 8 F2 A 19 220 8 F2 B 14 210 6 F2 C 12 212 5 F3 A 15 230 10 ; proc sort data=have out=temp nodupkey; by _all_; run; proc transpose data=temp out=temp1; by id marketed; var value: ; run; proc transpose data=temp1 out=want; by id; id _name_ marketed; var col1; run;
If you know in advance the list of marketing codes, then you can create a 2-ways matrix, with one column per marketing code, and 1 row for each original VALUE variable.
Associate that matrix with the desired variable names, and it's just a matter of copying data to the matrix:
data have;
input ID $ Marketed $ Value1 Value2 Value3;
datalines;
F1 A 13 207 1
F1 A 13 207 1
F1 A 13 207 1
F1 B 18 205 3
F1 B 18 205 3
F1 B 18 205 3
F1 B 18 205 3
F1 C 15 208 4
F1 C 15 208 4
F2 A 19 220 8
F2 A 19 220 8
F2 B 14 210 6
F2 C 12 212 5
F3 A 15 230 10
;
data want (drop=_: marketed value1-value3);
do until (last.id);
set have;
by id;
array values {3,3} Value1A Value1B Value1C
Value2A Value2B Value2C
Value3A Value3B Value3C ;
array orig {3} value1-value3;
select (marketed);
when ('A') _column=1;
when ('B') _column=2;
when ('C') _column=3;
end;
do _row=1 to 3; values{_row,_column}=orig{_row}; end;
end;
run;
Why does the input data have repeating observations with the exact same values?
And where do those repeating values go?
Please explain and provide detailed results that match exactly what you want from the given example input.
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.