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

[andreas_lds]

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.

[SIgnificatif]

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 :

[Tom]

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

[SIgnificatif]

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

[art297]

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:

[SIgnificatif]

the transpose macro is also not working ( surprisingly) since it seems it doesn't have the 'oocurs twice' problem.

It is missing variables : I have for instance 429 rows in data have, after the transpose macro I have 152 columns 😞 , they are ordered well ( blanks if there is no value on that variable) but oher variables just disappeared...

maybe reverting back and trying to find a solution with the proc transpose ?

[art297]

You will have to provide some example data that the macro isn't capturing.

The one thing I can think of is that your text_id field has some values that contain characters that aren't acceptable in SAS variable names. If that is the case, you can just include an extra parameter in the macro call statement, namely convertid=yes.

For example, the following works for me:

data have ;
  infile datalines missover dlm=',';
  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, var3, colt
14555, var3, colt
14555, var 4, hollywood
15555, first_name, Janne
15555, last_name, grey
15555, birht_date, 10091950, 10/9/1950
15555, var1, orange
15555, var1, orange
15555, var1, orange
15555, var1, orange
15555, sex, f, female
15555, var2, Xmen
15555, var5, light*yellow
16666, first_name, bruce
16666, last_name, banner
16666, var1, green
16666, var1, green
16666, var1, green
;

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


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;

proc print data=want;
run;

Art, CEO, AnalystFinder.com

[SIgnificatif]

Thank you for the response Dear Art 🙂

it is working

I have digged into it and I found what is happening, but need a fix, here is your code with some modification applied to the 'Hulk' ( since there is more than a green hulk in the comics 🙂 : I have modified and added :

16666, var1, green
16666, var1, grey
16666, var1, red

So the whole code will look kile

data have ;
  infile datalines missover dlm=',';
  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, var3, colt
14555, var3, colt
14555, var 4, hollywood
15555, first_name, Janne
15555, last_name, grey
15555, birht_date, 10091950, 10/9/1950
15555, var1, orange
15555, var1, orange
15555, var1, orange
15555, var1, orange
15555, sex, f, female
15555, var2, Xmen
15555, var5, light*yellow
16666, first_name, bruce
16666, last_name, banner
16666, var1, green
16666, var1, grey
16666, var1, red
;

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


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;

proc print data=want;
run;

How to loop trough var1 ? I mean print each color if its different ? I mean var1= green, var1=greay, var1= red ? also  it will be interesting to count the var1 and print the number, like var1_total = 3 ( to use them in future calculations eg, use with date) if the hulk changed a color in each date 🙂  and create new tables... 

Thanks again for your response

[art297]

What I think you want is beyond the scope of the macro, but can be accomplished by adding a proc sort and data step before running the macro. e.g.:

data have ;
  infile datalines missover dlm=',';
  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, var3, colt
14555, var3, colt
14555, var 4, hollywood
15555, first_name, Janne
15555, last_name, gray
15555, birht_date, 10091950, 10/9/1950
15555, var1, orange
15555, var1, orange
15555, var1, orange
15555, var1, orange
15555, sex, f, female
15555, var2, Xmen
15555, var5, light*yellow
16666, first_name, bruce
16666, last_name, banner
16666, var1, green
16666, var1, gray
16666, var1, red
;

proc sort data=have out=need nodupkey;
  by id text_id value;
run;

data need (drop=_:);
  length value $36;
  retain value;
  set need(rename=(value=_value));
  by id text_id;
  if first.text_id then value=_value;
  else value=catx(',',value,_value);
run;

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


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;

proc print data=want;
run;

That should come close to what I think you want.

Art, CEO, AnalystFinder.com