topic Re: Tranpose all variables in SAS Procedures

Tranpose all variables

sahoositaram555 — Tue, 24 Mar 2020 21:11:13 GMT

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

01002 potassium

Re: Tranpose all variables

art297 — Tue, 24 Mar 2020 21:22:28 GMT

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

Re: Tranpose all variables

JerryV — Tue, 24 Mar 2020 21:29:28 GMT

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;

Re: Tranpose all variables

art297 — Tue, 24 Mar 2020 21:37:11 GMT

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

Re: Tranpose all variables

Patrick — Wed, 25 Mar 2020 05:26:13 GMT

@art297

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

Re: Tranpose all variables

sahoositaram555 — Wed, 25 Mar 2020 05:27:38 GMT

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.

Re: Tranpose all variables

sahoositaram555 — Wed, 25 Mar 2020 07:12:07 GMT

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.

Re: Tranpose all variables

sahoositaram555 — Wed, 25 Mar 2020 07:11:18 GMT

Thank you. It's working fine.

Re: Tranpose all variables

Patrick — Wed, 25 Mar 2020 10:13:11 GMT

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

Re: Tranpose all variables

sahoositaram555 — Wed, 25 Mar 2020 10:18:26 GMT

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.

Re: Tranpose all variables

Patrick — Wed, 25 Mar 2020 11:01:00 GMT

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;

Re: Tranpose all variables

sahoositaram555 — Wed, 25 Mar 2020 11:44:21 GMT

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