Column Transposition if the number of variables is fluctuating or another way to do a transposition

[SIgnificatif]

Hi All.

I would like to perform a transposition

proc transpose
 data = work.have
 out = work.want_t;
var var1, name, home......
run;

But I found multiple problems:

1. entries that have different amount of variables
2. I found errors : variable : not found and variable_name contains more than 32 characters

What do you do in this case ? if there is transposition problems.

are there other way to perform a transposition ?

using indexes and adding values in rows ? < this could be interesting because the number of columns of the havev dataset that belongs to one entry is too variable.

Thank you for response

[PaigeMiller]

---

@SIgnificatif wrote:

Hi All.

I would like to perform a transposition

proc transpose
 data = work.have
 out = work.want_t;
var var1, name, home......
run;

But I found multiple problems:

1. entries that have different amount of variables

---

What "entries" do you mean? Can you give an example?

I found errors : variable : not found and variable_name contains more than 32 characters

So WORK.HAVE has variable names with more than 32 characters? I did not think that was possible. Please explain.

[SIgnificatif]

Thank you for response

the entries are included in the columns:

first_name last_name variable1_belongs_to_that_person variable2_belongs_to_that_person variable3_belongs_to_that_person variable4_belongs_to_that_person and so on then another first_name last_name variable comes in with columns that belongs to this person.. and it's reapeating.

1. the number of columns that belongs to subjects differs
2. if there is no transposition way how to transform these columnt into rows ? 

The idea is to transpose : trasnform columns into rows , to add sens to the data AND perform some basic classification.

regarding 32 characters, i just used the var followed by names of the columns , I believe their number was too long...

[PaigeMiller]

---

@SIgnificatif wrote:

Thank you for response

the entries are included in the columns:

first_name last_name variable1_belongs_to_that_person variable2_belongs_to_that_person variable3_belongs_to_that_person variable4_belongs_to_that_person and so on then another first_name last_name variable comes in with columns that belongs to this person.. and it's reapeating.

1. the number of columns that belongs to subjects differs

---

Again, I'm not understanding. In a SAS data set, each row has the exact same number of columns (although the values in a given column can be missing). Can you show me a small example?

regarding 32 characters, i just used the var followed by names of the columns , I believe their number was too long...

Are we talking about variables in SAS data sets here? Can you show me a PROC CONTENTS output showing the variable name with more than 32 characters?

[SIgnificatif]

Thank you for the response,

first_name last_name , they are actually in rows format, i would like to transpose them ( sort of order by..)

sorry about the long variables, it's not the problem right now, the problem is if the variables are missing - because I name all the variables when I try to transpose.  (as I told you the number of rows differs based on the variables first_name and last_name

[PaigeMiller]

Show me a small example.

[SIgnificatif]

Thank you for the precision.

Here is the example,

I would like to use data from table want into new one with this example, in fact it will be more complex, but I'd like to start with simple things 🙂

As You see the variables that belongs to first Id differs from the second ID...
Thanks again.

[PaigeMiller]

What you are showing is the SAS data set you are working with? Or is it an Excel file? Is it just a list that you made up to illustrate the problem?

[Tom]

It is really hard to program from photographs of data. Sounds like you have this structure.

data have;
  infile cards dsd dlm='|';
  length name $32 value $50 ;
cards;
id|1234
first_name|Joe
last_name|Smith
id|2345
first_name|Sue
last_name|Jones
;

The first thing you need to before transposing is a variable that can be used to group the values for the same observation together.

So if you data looks like above then the new group starts when NAME='id'.

data step1;
  set have;
  if name='id' then block+1;
run;

Now you can use BLOCK as the BY variable for PROC TRANSPOSE.

proc transpose data=step1 out=want;
  by block;
  id name;
  var value;
run;

A couple of issues you might have.

1) The value of NAME is longer than 32 characters.  If so then you cannot use it as the name of the new variable.  You might be able to use it as the LABEL of the new variable.

2) You might have more than one value for the same NAME in the same BLOCK.  You might set away with use the LET option on PROC TRANSPOSE, but then you will lose some data.  You might need to convert your NAME value into unique names. Or break the data into more blocks.

[ballardw]

---

@SIgnificatif wrote:

Hi All.

I would like to perform a transposition

proc transpose
 data = work.have
 out = work.want_t;
var var1, name, home......
run;

But I found multiple problems:

1. entries that have different amount of variables
2. I found errors : variable : not found and variable_name contains more than 32 characters

What do you do in this case ? if there is transposition problems.

are there other way to perform a transposition ?

using indexes and adding values in rows ? < this could be interesting because the number of columns of the havev dataset that belongs to one entry is too variable.

Thank you for response

---

I think you may be skipping a couple of steps in your description. If you get an error that of variable not found then that implies that you listed a variable in a procedure. Similar that you entered a variable name of more than 32 characters for some reason. So the question is why? A very common cause of this is reliance on Proc Import with unreliable data sources like spreadsheets where people create sheets with changing columns and column headings.

When you are getting errors with code you should copy the code and the error messages all from the log and paste into a code box on the forum opened with the {I} or the "running man" icon. The code box is important to preserve formatting of the message and the diagnostic elements that SAS often provides. The forum main message windows will reformat text making those diagnostics of limited use.

[art297]

Difficult to guess what you are trying to do, but I'll take a stab. If your data looks something like:

data have;
  informat var value $32.;
  input var value;
  cards;
id 1
first_name John
last_name Doe
var1 15
var2 10
var3 5
var4 8
id 2
first_name Mary
last_name Doe
var1 6
var2 20
id 3
first_name Ralph
last_name Doe
var1 7
var2 4
var3 16
;

then I would first use a simple data step to (1) create an id variable and (2) copy it to all records until a new id is found. i.e.:

data need;
  set have;
  length id $32;
  retain id;
  if var eq 'id' then id=value;
run;

then, finally, I'd use the %transpose macro (that you can copy and paste from: https://communities.sas.com/t5/SAS-Communities-Library/A-better-way-to-FLIP-i-e-transpose-make-wide-...

the actual macro call, given the above, would be:

%transpose(data=need, out=want, by=id, autovars=all, 
   id=var, use_varname=no)

That would create the wide file you described as the one you are seeking.

HTH,

Art, CEO, AnalystFinder.com

[SIgnificatif]

Thanks Dear All for your great ideas,

I'm still brainstorming it, will return to you after i've tried some steps.

1) I found that the column data got mixed if a new variable appears ( because of difference of number of variables for different Id's), I'm thinking to create a sort of condition ( but still don"t know how to write the conditions to create an empty record if a variable of one of the ID's is missing.

2) Did not concider if the name could be the same, is this a real concern ? what could be another solution to that ? create temporary variables for first name and last name ? ( maybe I have not understood well this ..)

Thank you and have a wonderful week 🙂

[SIgnificatif]

Update.

My apologies,
I havae recreated the table and the results differs from the first post.

here is the image of what data I want - the first step ( because i'll have to perform some calculations afterward)

Thanks for your support.

[andreas_lds]

---

@SIgnificatif wrote:

Update.

My apologies,
I havae recreated the table and the results differs from the first post.

here is the image of what data I want - the first step ( because i'll have to perform some calculations afterward)

Thanks for your support.

---

The format you have right now is - for many calculations done with procedures - the most useful one. So before wasting time in transposing data, it could be good idea to explain the calculations you have to do, maybe transposing is not necessary at all. The concepts used to work with data are differ between excel and SAS, understanding those differences is the key to create efficient and easy to maintain code.

[SIgnificatif]

Hi, thank for clarification,

I believe that I'll need the transposition ( with the proc or the macro one that is mentioned here thanks to Art , I mean I have to transpose from vertical to horizontal anyway...

there will not be too complicated calculations, I mean something like this: