Tranpose all variables

[sahoositaram555]

Dear All,

I have a dataset like posted below. 

data one;

input subjid sodium potassium bicarbonate glucose bloodureanitrogen creatinine SGPT SGOT GGT;

cards;

01001 12.2 34.2 33.2 33 22 123 90 89 89.3

01002 13.2 34.2 32.2 23 33 234 45 34 23.2

;

run; 

I would like to produce :

subjid variable col1

01001 sodium  12.2

01001 potasium  34.2

01001 bicarbonate 33.2

01001 glucose 33

01001 ..

..... 

01002 Sodium 13.2

01002 potasium  34.2

01002 bicarbonate  32.2

01002 glucose...

........

i tried with proc transpose but didn't work, So below code i have used

proc transpose data=one; 

by subjid;

var _all_;

run;

any suggestions?

I would like to produce :

subjid variable col1

01001 sodium 

01001 sodium 

01001 sodium 

01001 sodium 

01001 sodium 

01002 potassium

[art297]

Add an out= option, as well as a name= option. e.g.:

proc transpose data=one out=want name=variable; 
  by subjid;
  var _all_;
run;

Art, CEO, AnalystFinder.com

[JerryV]

Building on art297's response, instead of using var _all_; specify each variable.  otherwise you'll get a row for subjid too.

var sodium potassium bicarbonate glucose bloodureanitrogen creatinine SGPT SGOT GGT;

[art297]

I agree with @JerryV , but you can also use a variable list. e.g.:

proc transpose data=one out=want name=variable; 
  by subjid;
  var sodium--GGT;
run;

Art, CEO, AnalystFinder.com

[Patrick]

@art297 

This variable list is only going to work if subjid precedes sodium in the PDV. 

[sahoositaram555]

Hi @Patrick ,

 i also realized that the same code with an id statement doesn't work when we have subjid sorted by both subjid and visits. eg: please see below

data a;

input subjid visitc sodium potassium bicarbonate glucose bloodureanitrogen creatinine SGPT SGOT GGT;

cards;

01001 3th_visit 12.2 34.2 33.2 33 22 123 90 89 89.3

01001 6th_visit 22.3 41.6 56.1 89 12 133 80 49 39.3

01001 9th_visit 20.2 40.2 23.2 23 12 230 30 99 19.3

01002 3th_visit 13.2 41.2 12.2 13 43 634 65 14 23.2

01002 6th_visit 90.2 21.2 33.2 33 22 123 90 89 89.3

01002 9th_visit 29.2 34.2 30.2 13 12 126 30 29 99.3

;

run; 

when i transpose this by below code:

proc transpose data=a out=t_a name=variables ;
by subjid;
var sodium--ggt;
id visit;
run;

i'm getting in the output where subjid is coming and id variable visit is coming appropriately but the values are not coming, they are blank.

any thoughts?

@art297  it would be great to have your response aswell.

[Patrick]

---

@sahoositaram555 wrote:

Hi @Patrick ,

 i also realized that the same code with an id statement doesn't work when we have subjid sorted by both subjid and visits. eg: please see below

That's because visitc is already missing in your source table A. You need to use a character informat in order to read the values for visitc.

Once the code is fixed things work as desired.

data a;
  input subjid visitc:$20. sodium potassium bicarbonate 
        glucose bloodureanitrogen creatinine SGPT SGOT GGT;
  cards;
01001 3th_visit 12.2 34.2 33.2 33 22 123 90 89 89.3
01001 6th_visit 22.3 41.6 56.1 89 12 133 80 49 39.3
01001 9th_visit 20.2 40.2 23.2 23 12 230 30 99 19.3
01002 3th_visit 13.2 41.2 12.2 13 43 634 65 14 23.2
01002 6th_visit 90.2 21.2 33.2 33 22 123 90 89 89.3
01002 9th_visit 29.2 34.2 30.2 13 12 126 30 29 99.3
;

proc transpose data=a out=t_a name=variables;
  by subjid;
  id visitc;
  var sodium--ggt;
run;

proc print data=t_a ;
run;

[sahoositaram555]

Hi @Patrick,
Thank your for your reply. I have tried this. what i get to see is there are 3 columns named as 3thvisit 6thvisit 9thvisit are getting created but they all have length of 5000 and the values , which i can't use for any of the derivation , neither i can use any function to extract the value. example if i use a cat function or compress function to reduce the length or even i tried by assigning a lesser length by mentioning it before set statement so that PDV get the shorter length but it's not working. i have tried to append the value to other variable, but its putting missing value there as well.

[Patrick · Accepted solution]

Hi @sahoositaram555 

The variables I'm getting are numeric with a length of 8 (see screenshots below). I've also made Proc Transpose adding an underscore to the created variable names so that you end up with valid SAS variable names.

Another option is to use the %transpose() macro as documented here - and should you have problems using it then @art297 can certainly support you.

data a;
  input subjid visitc:$20. sodium potassium bicarbonate 
        glucose bloodureanitrogen creatinine SGPT SGOT GGT;
  cards;
01001 3th_visit 12.2 34.2 33.2 33 22 123 90 89 89.3
01001 6th_visit 22.3 41.6 56.1 89 12 133 80 49 39.3
01001 9th_visit 20.2 40.2 23.2 23 12 230 30 99 19.3
01002 3th_visit 13.2 41.2 12.2 13 43 634 65 14 23.2
01002 6th_visit 90.2 21.2 33.2 33 22 123 90 89 89.3
01002 9th_visit 29.2 34.2 30.2 13 12 126 30 29 99.3
;

proc transpose data=a out=t_a name=variables prefix=_;
  by subjid;
  id visitc;
  var sodium--ggt;
run;

proc print data=t_a ;
run;

proc contents data=t_a;
run;

 

[sahoositaram555]

Thanks @Patrick . glad to see your response. it worked.

[sahoositaram555]

Thank you @art297.it really worked. however i'm curious to hear from you about is there any other functionality of using name=Variable option apart from renaming _name_ to Variable,probaly any string reason which i'm missing out ?

Hope to hear from you . thanks in advance.

[sahoositaram555]

Thank you. It's working fine.