Hello
I want transpose data from row to another column specific to row
Please help cod how we can do that one in array or transpose
My data was attached in excel sheet
In this Excel sheet I want create new columns that are ALT , AST , ALT _date, AST_date, ALT_VISIT , AST_Visit
This ALT and AST values need to populate for respective BILDIR test like below.
USUBJID |
LBTESTCD |
LBTEST |
LBSTRESN |
LBSTRESC |
VISIT |
VISITNUM |
LBDTC |
ALT |
AST |
ALT_Date |
AST_Date |
ALT_VISIT |
AST_VISIT |
Sci-B-Vac-001-100-1001 |
ALT |
Alanine Aminotransferase |
16 |
16 |
SCREENING |
10 |
2018-02-28 |
|
|
|
|
|
|
Sci-B-Vac-001-100-1001 |
ALT |
Alanine Aminotransferase |
11 |
11 |
VISIT 1 |
20 |
2018-03-27 |
|
|
|
|
|
|
Sci-B-Vac-001-100-1001 |
ALT |
Alanine Aminotransferase |
13 |
13 |
SUBSTUDY1 |
40 |
2018-04-03 |
|
|
|
|
|
|
Sci-B-Vac-001-100-1001 |
ALT |
Alanine Aminotransferase |
16 |
16 |
SUBSTUDY2 |
70 |
2018-05-03 |
|
|
|
|
|
|
Sci-B-Vac-001-100-1001 |
ALT |
Alanine Aminotransferase |
17 |
17 |
SUBSTUDY3 |
110 |
2018-09-18 |
|
|
|
|
|
|
Sci-B-Vac-001-100-1001 |
AST |
Aspartate Aminotransferase |
19 |
19 |
SCREENING |
10 |
2018-02-28 |
|
|
|
|
|
|
Sci-B-Vac-001-100-1001 |
AST |
Aspartate Aminotransferase |
16 |
16 |
VISIT 1 |
20 |
2018-03-27 |
|
|
|
|
|
|
Sci-B-Vac-001-100-1001 |
AST |
Aspartate Aminotransferase |
17 |
17 |
SUBSTUDY1 |
40 |
2018-04-03 |
|
|
|
|
|
|
Sci-B-Vac-001-100-1001 |
AST |
Aspartate Aminotransferase |
21 |
21 |
SUBSTUDY2 |
70 |
2018-05-03 |
|
|
|
|
|
|
Sci-B-Vac-001-100-1001 |
AST |
Aspartate Aminotransferase |
20 |
20 |
SUBSTUDY3 |
110 |
2018-09-18 |
|
|
|
|
|
|
Sci-B-Vac-001-100-1001 |
BILDIR |
Direct Bilirubin |
1.9 |
1.9 |
SCREENING |
10 |
2018-02-28 |
16 |
19 |
2018-02-28 |
2018-03-27 |
SCREENING |
SCREENING |
Sci-B-Vac-001-100-1001 |
BILDIR |
Direct Bilirubin |
1.9 |
1.9 |
VISIT 1 |
20 |
2018-03-27 |
11 |
16 |
2018-03-27 |
2018-03-27 |
VISIT 1 |
VISIT 1 |
Sci-B-Vac-001-100-1001 |
BILDIR |
Direct Bilirubin |
1.7 |
1.7 |
SUBSTUDY1 |
40 |
2018-04-03 |
13 |
17 |
2018-04-03 |
2018-03-27 |
SUBSTUDY1 |
SUBSTUDY1 |
Sci-B-Vac-001-100-1001 |
BILDIR |
Direct Bilirubin |
|
|
SUBSTUDY2 |
70 |
2018-05-03 |
16 |
21 |
2018-05-03 |
2018-05-03 |
SUBSTUDY2 |
SUBSTUDY2 |
Sci-B-Vac-001-100-1001 |
BILDIR |
Direct Bilirubin |
|
|
SUBSTUDY3 |
110 |
2018-09-18 |
17 |
20 |
2018-09-18 |
2018-09-18 |
SUBSTUDY3 |
SUBSTUDY3 |
Sci-B-Vac-001-100-1001 |
BILI |
Bilirubin |
11.5 |
11.5 |
SCREENING |
10 |
2018-02-28 |
|
|
|
|
|
|
Sci-B-Vac-001-100-1001 |
BILI |
Bilirubin |
5.3 |
5.3 |
VISIT 1 |
20 |
2018-03-27 |
|
|
|
|
|
|
Sci-B-Vac-001-100-1001 |
BILI |
Bilirubin |
9.7 |
9.7 |
SUBSTUDY1 |
40 |
2018-04-03 |
|
|
|
|
|
|
Sci-B-Vac-001-100-1001 |
BILI |
Bilirubin |
5 |
5 |
SUBSTUDY2 |
70 |
2018-05-03 |
|
|
|
|
|
|
Sci-B-Vac-001-100-1001 |
BILI |
Bilirubin |
7 |
7 |
SUBSTUDY3 |
110 |
2018-09-18 |
|
|
|
|
|
|
Sci-B-Vac-001-100-1001 |
BILI |
Bilirubin |
|
NEGATIVE |
SCREENING |
10 |
2018-02-28 |
|
|
|
|
|
|
Please provide example data in usable form (data step with datalines). Since the virus scan blocks your Excel, it is even more useless than usual.
Keep in mind that this is a SAS support forum, Excel questions are best dealt with at microsoft.com.
First, please post test data - i.e. a small sample of test data which illustrates your problem, post this test data in the form of a datastep:
Do not post real data or Excel files.
Next up, show what you want out from this test data.
At a glance at what you have posted there, you don't want a "tranpose" at all. You basically want to merge some data back onto the main data, something like:
data want; set have have (where=(lbtestcd="ALT" and visit="SCREENING") rename=(lbstresn=alt ...) keep=usubjid alt) have (where=(lbtestcd="AST" and visit="SCREENING") rename=(lbstresn=ast ...) keep=usubjid ast); by usubjid; run;
Note this just merges on baseline to each record, you would need to be far more specific about your issue for any more accurate examples and provide usable test data.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.