Transpose data with condition

[#1]

Hi everyone,

Thanks in advance for reading this thread. I have been trying to find a solution to my case for a while, and I will be very glad to any help.

The table tbl_partners contains some data from the partners of our customers, like the percentage (%) share, level, number and type. Each customer has a distinct ID (first variable in Layout A observation).

But curiously this table came with data separated in almost 900 variables for each ID, instead of transposed observations. I need to transpose this table from Layout A to Layout B, so then I will be able to extract data to create a specific report.

I'm trying to use PROC TRANSPOSE BY GROUPS, but the problem is that the table has almost 900 variables and in many cases this variables are "0", what indicates that the Partner_X doesn't exist.

In fact, the problem is that every ID has his own number of partners from Partner_01 (red color) to a maximum of 100.

So I have to transpose information's only when PARTNER_X_LEVEL variable is grater than 0, because in that case I will be sure that the partner exists. For example, in the obs below, the ID 999999 has 2 partner's so Partner_03 (green color) doesn't exist and came with zeros "0", so in that case I have to transpose infos only from Partner_01 (red color) and Partner 02 (blue color) what will generate Layout 2 table.

I'm using Enterprise Guide 4.3

Thank you very much, regards from Brazil.

• Layout A

Id	Name	Partner_01 Level	Partner_01 Number	Partner_01 Type	Var A	Var B	Var C	Var D	Partner_01 Share	Partner_02 Level	Partner_02 Number	Partner_02 Type	Var AA	Var BB	Var CC	Var DD	Partner_02 Share	Partner_03 Level	Partner_03 Number	Partner_03 Type	Var AAA	Var BBB	Var CCC	Var DDD
999999	Example	1	777777	2	X	X	X	X	50%	2	888888	1	X	X	X	X	90%	0	0	0	0	0	0	0

• Layout B

Id	Name	Partner Level	Partner Number	Partner Type	Var A	Var B	Var C	Var D	Partner Share
999999	Example	1	777777	2	X	X	X	X	50%
999999	Example	2	888888	1	X	X	X	X	90%

[TomKari]

Interesting problem!

I wouldn't really call this a transposition; that would be more if the data were in the form

Id VarAA x
Id VarBB x
Id VarCC x

and you want to convert it to the format that you've got. Rather, in your case, what should be distinct records are concatenated.

I suggest you use the query builder instead:

   1. Create one result of Id and all of the Partner 01 variables, where Partner_01Level > 0. Then do the same for Partner 2, Partner 3, etc.

   2. Once you have these tables, concatenate (UNION) them together.

That's the only way I can think of to do it with EG's point and click options. It will unfortunately be a pain because of the number of possible partners.

On the other hand, if you're comfortable submitting SAS code through an EG code window, this job can be done much more easily using SAS macros. Let us know if this is the case.

Tom

[Reeza]

If you post some sample data in a useable format someone can help with the code. Right now you've provided variable labels, variable names will be important, especially the VARA-VARD names as I'm not sure what naming convention is used for those variables (You can't have VARA*100 as the variable name would be too long for SAS).

As has indicated you could use a macro. 

It seems to me your table will always have 100 records with empty values for ones that aren't available.  You could use the SASHELP.VCOLUMN table to get the column names and create 8 arrays and then use those to flip in a data step.

Or you could use CALL VNEXT if your data structure is consistent but again the naming convention is required.

[Ksharp · Accepted solution]

Double proc transpose .

data have;
input id name $ level1 number1 level2 number2 level3 number3;
cards;
1 xx 1 2 2 3 0 0
2 xx 1 2 2 3 0 0
;
run;
proc transpose data=have out=temp;
by id name;
run;
data temp;
 set temp;
 by id name;
 retain flag lev 0;
 length vname $ 20;
 vname=compress(_name_,,'ka');
 if first.name then flag=0;
 if _name_ eq: 'level' then do;
  if col1=0 then flag=1;
   else do;flag=0;lev=col1;end;
 end;
run;
proc transpose data=temp(where=(flag=0)) out=want;
by id name lev;
id vname;
var col1;
run;

Xia Keshan

[Reeza]

I'm guessing there's going to be a mix of data types - character and number, but the OP is in a better position to answer that.

[art297]

You could try something like the following:

proc transpose data=have out=long;

  by id name;

  var Partner_01_Level--Partner_03_Share;

run;

data long;

  set long;

  by id name;

  retain keepit level;

  set long ( firstobs = 2 keep = Col1 rename = (Col1 = Next_Col1) )

      long (      obs = 1 drop = _all_ );

  Col1=strip(Col1);

  if find(_name_,'Level') then do;

    keepit=1;

    _name_='Level';

    if Col1 eq 0 and Next_Col1 eq 0 then keepit=0;

    else level=Col1;

  end;

  else do;

    if find(_name_,'Number') then _name_='Number';

    if find(_name_,'Type') then _name_='Type';

    if find(_name_,'Share') then _name_='Share';

    if find(_name_,'var') then _name_=substr(_name_,1,5);

    if keepit then output;

  end;

run;

proc transpose data=long out=want (drop=_:);

  by id name level;

  var col1;

run;

[#7]

First I would like to thank you all for those fast answers, they helped me a lot! I'm really thankful for that!

Now I'm trying to test all solutions that you gave me here and I will post the results as soon as I can, now I'm using the university edition, I'm going to have access to 4.3EG again and to real data only on Monday, when I will be back at the office.

Reeza, "Name" is string and all others are numeric, the "%" symbol I had added to this post just to exemplify, as I did with the VARA, VARB etc. These generic variables (VARA, VARB etc) have the same name for all the 100 partners variables, with a number instead of letter. For example, VARA01, VARB01 for Partner_01 and VARA100, VARB100 for Partner_100. Sorry if I was not clear.

Regards

[art297]

I modified the code I originally posted to account for all of the variables, except name, to be numeric.

[#9]

Here are the results, everything works perfect, so thank you all for spending your time to help with this!

Regards

/* 04/05/2015 - Credits to https://communities.sas.com/message/266407#266407 */

data have;

input Id Name $ Level_Partner_01 Number_Partner_01 Type_Partner_01 Vara_01 Varb_01 Varc_01 Vard_01 Share_Partner_01 Level_Partner_02 Number_Partner_02 Type_Partner_02 Vara_02 Varb_02 Varc_02 Vard_02 Share_Partner_02 Level_Partner_03 Number_Partner_03 Type_Partner_03 Vara_03 Varb_03 Varc_03 Vard_03 Share_Partner_03 Level_Partner_04 Number_Partner_04 Type_Partner_04 Vara_04 Varb_04 Varc_04 Vard_04 Share_Partner_04;

cards;

111111 CompanyA 1 111111 1 1 1 1 1 10 2 222222 2 2 2 2 2 20 3 333333 3 3 3 3 3 30 0 0 0 0 0 0 0 0

222222 CompanyB 4 444444 4 4 4 4 4 40 5 555555 5 5 5 5 5 50 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

333333 CompanyC 3 666666 6 6 6 6 6 60 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

;

run;

proc transpose data=have out=temp;

by Id Name;

run;

data temp;

set temp;

by Id Name;

retain flag lev 0;

length vname $ 20;

vname=compress(_name_,,'ka');

if first.Name then flag=0;

if _name_ eq: 'Level' then do;

  if col1=0 then flag=1;

  /*falta entender isso*/

   else do;flag=0;lev=col1;end;

end;

run;

proc transpose data=temp(where=(flag=0)) out=want;

by id name lev;

id vname;

var col1;

run;