topic Re: Column Transposition if the number of variables is fluctuating or another way to do a transposit in New SAS User

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

SIgnificatif — Fri, 19 Jul 2019 13:19:03 GMT

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:

entries that have different amount of variables
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

Re: Column Transposition if the number of variables is fluctuating or another way to do a transposit

PaigeMiller — Fri, 19 Jul 2019 13:23:55 GMT

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

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.

Re: Column Transposition if the number of variables is fluctuating or another way to do a transposit

SIgnificatif — Fri, 19 Jul 2019 13:31:44 GMT

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.

the number of columns that belongs to subjects differs
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...

Re: Column Transposition if the number of variables is fluctuating or another way to do a transposit

PaigeMiller — Fri, 19 Jul 2019 13:46:45 GMT

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

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?

Re: Column Transposition if the number of variables is fluctuating or another way to do a transposit

SIgnificatif — Fri, 19 Jul 2019 14:06:48 GMT

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

Re: Column Transposition if the number of variables is fluctuating or another way to do a transposit

PaigeMiller — Fri, 19 Jul 2019 14:11:00 GMT

Show me a small example.

Re: Column Transposition if the number of variables is fluctuating or another way to do a transposit

SIgnificatif — Fri, 19 Jul 2019 14:24:09 GMT

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.

Re: Column Transposition if the number of variables is fluctuating or another way to do a transposit

PaigeMiller — Fri, 19 Jul 2019 14:28:00 GMT

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?

Re: Column Transposition if the number of variables is fluctuating or another way to do a transposit

ballardw — Fri, 19 Jul 2019 14:34:12 GMT

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

entries that have different amount of variables

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.

Re: Column Transposition if the number of variables is fluctuating or another way to do a transposit

art297 — Fri, 19 Jul 2019 21:06:22 GMT

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-a-dataset/ta-p/433620?title=A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset

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

Re: Column Transposition if the number of variables is fluctuating or another way to do a transposit

Tom — Fri, 19 Jul 2019 23:16:09 GMT

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.

Re: Column Transposition if the number of variables is fluctuating or another way to do a transposit

SIgnificatif — Mon, 22 Jul 2019 07:44:16 GMT

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 🙂

Re: Column Transposition if the number of variables is fluctuating or another way to do a transposit

SIgnificatif — Mon, 22 Jul 2019 08:25:03 GMT

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.

Re: Column Transposition if the number of variables is fluctuating or another way to do a transposit

andreas_lds — Mon, 22 Jul 2019 10:06:11 GMT

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

Re: Column Transposition if the number of variables is fluctuating or another way to do a transposit

SIgnificatif — Mon, 22 Jul 2019 10:51:01 GMT

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:

Re: Column Transposition if the number of variables is fluctuating or another way to do a transposit

andreas_lds — Mon, 22 Jul 2019 11:16:37 GMT

It is high-time that you post data in usable form (a data step using datalines statement, NOT an excel-file) and what do expect/need as result.

For combining var1 with sex transposing is not necessary - it could be done with a data step using BY and RETAIN, but if you need a report in the presented form, then yes, you will have to transpose the data.

Re: Column Transposition if the number of variables is fluctuating or another way to do a transposit

SIgnificatif — Mon, 22 Jul 2019 14:51:06 GMT

data have ;
infile datalines missover;
length ID   text_id $12   value $12  details  $12;
input   ID   text_id $   value $  details  $;
 datalines;
14555 first_name john
14555 last_name smith
14555 var1 white
14555 sex m male
14555 var3 colt
14555 var4 hollywood
15555 first_name Janne
15555 last_name grey
15555 birht_date 10091950 10/9/1950
15555 var1 orange
15555 sex f female
15555 var2 Xmen
16666 first_name bruce
16666 last_name banner
16666 var1 green
;
   run;

Thanks forthe clarification, the abovementioned code is the have data.

thanks for your return :

Re: Column Transposition if the number of variables is fluctuating or another way to do a transposit

Tom — Mon, 22 Jul 2019 14:57:26 GMT

So did you run the PROC TRANSPOSE code? What happened?

Here is code for your posted data.

proc transpose data=have out=want ;
  by id ;
  var value;
  id text_id ;
run;

Results:

                          first_    last_                                            birht_
Obs     ID      _NAME_     name      name      var1     sex    var3      var4         date      var2

 1     14555    value     john      smith     white      m     colt    hollywood
 2     15555    value     Janne     grey      orange     f                          10091950    Xmen
 3     16666    value     bruce     banner    green

Re: Column Transposition if the number of variables is fluctuating or another way to do a transposit

art297 — Mon, 22 Jul 2019 16:00:44 GMT

One of the main differences between the transpose procedure, and the transpose macro, is that the macro automatically orders numbered lists (like var1-var4) .. regardless of the order in which they appear in the data.

You initially indicated that ID wasn't on every record, but now you are showing that it is already on each record.

Your detail variable looks like it represents the values for a SAS format. If there aren't too many, I'd just create a format (e.g., in your example data, I'd create a format for sex). As for birth_date (or, as you indicated, birht_date), I'd convert it to be a SAS date. However, you'll have to know whether it represents mmddyyyy or ddmmyyyy.

Here is the call you'd use if you were going to use the macro:

%transpose(data=have, out=need, by=id, var=value, 
   id=text_id, use_varname=no)


proc format;
  value $sex
  'm'='Male'
  'f'='Female'
  ;

run;

data want(drop=_:);
  set need(rename=(birht_date=_birht_date));
  birth_date=input(_birht_date,mmddyy8.);
  format birth_date date9.;
  format sex $sex.;
run;

The final file (i.e., the file called want) would appear as:

Re: Column Transposition if the number of variables is fluctuating or another way to do a transposit

SIgnificatif — Tue, 23 Jul 2019 07:26:35 GMT

Thanks for the reply,

1) but this is not working because name of the variable:

text_id

occures twice or more, ex: same subject could have 'color' many times...

2) Also i'm interested to keep the names of the variables in text_id 1 time only at the top ..

Thank you again