BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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;

Patrick_0-1585133314202.png 

Patrick_1-1585133355777.png

 

 

 

 

 

View solution in original post

11 REPLIES 11
art297
Opal | Level 21

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
SAS Employee

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
Opal | Level 21

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
Opal | Level 21

@art297 

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

sahoositaram555
Pyrite | Level 9

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
Opal | Level 21

 


@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
Pyrite | Level 9
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
Opal | Level 21

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;

Patrick_0-1585133314202.png 

Patrick_1-1585133355777.png

 

 

 

 

 

sahoositaram555
Pyrite | Level 9
Thanks @Patrick . glad to see your response. it worked.
sahoositaram555
Pyrite | Level 9
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
Pyrite | Level 9
Thank you. It's working fine.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 11 replies
  • 1132 views
  • 2 likes
  • 4 in conversation