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
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;
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
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;
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
This variable list is only going to work if subjid precedes sodium in the PDV.
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.
@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;
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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.