topic Re: Ttranspose data by array in SAS Programming

Ttranspose data by array

RajasekharReddy — Tue, 29 Jan 2019 06:43:05 GMT

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

Re: Ttranspose data by array

Kurt_Bremser — Tue, 29 Jan 2019 09:38:50 GMT

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.

Re: Ttranspose data by array

RW9 — Tue, 29 Jan 2019 09:39:01 GMT

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:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

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.