topic Re: How to transpose from same row with multiple variables? in SAS Programming

How to transpose from same row with multiple variables?

Moraes86 — Fri, 30 Jul 2021 20:28:50 GMT

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

Re: How to transpose from same row with multiple variables?

PaigeMiller — Fri, 30 Jul 2021 20:47:47 GMT

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?

Re: How to transpose from same row with multiple variables?

ballardw — Fri, 30 Jul 2021 20:54:21 GMT

Especially why the many rows of missing values?

Re: How to transpose from same row with multiple variables?

Moraes86 — Fri, 30 Jul 2021 21:12:56 GMT

I forgot to fill these rows....probably those rows would be filled with the same values

Re: How to transpose from same row with multiple variables?

Moraes86 — Fri, 30 Jul 2021 21:15:27 GMT

Yes... I understand what you mean...
I need to summarize these information in a single line
If you have another suggestion that I could use in this situation instead of Transpose.... I accept as well =D

Re: How to transpose from same row with multiple variables?

PaigeMiller — Fri, 30 Jul 2021 21:29:13 GMT

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?

Re: How to transpose from same row with multiple variables?

Patrick — Sat, 31 Jul 2021 02:00:56 GMT

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

Re: How to transpose from same row with multiple variables?

PGStats — Sat, 31 Jul 2021 04:02:09 GMT

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;

Re: How to transpose from same row with multiple variables?

Ksharp — Sat, 31 Jul 2021 12:02:19 GMT

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;

Re: How to transpose from same row with multiple variables?

mkeintz — Sat, 31 Jul 2021 20:27:14 GMT

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;

Re: How to transpose from same row with multiple variables?

Tom — Sat, 31 Jul 2021 18:11:47 GMT

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.

Re: How to transpose from same row with multiple variables?

Moraes86 — Mon, 09 Aug 2021 21:27:50 GMT

Thanks all for the valuable help!