topic Transposing long to wide in New SAS User

Transposing long to wide

MCandun — Mon, 10 Oct 2022 07:33:44 GMT

I'm using SAS-EG (version: 7.15 HF3 7.100.5.6132) and have imported my data (from an excel file).

The data I have looks like this:

ID	Intake_Date	Closed_Date	Outcome	Result	Outcome_time_spent	Case Status
110	2022-04-01	2022-05-01	Consult	Individual	30	Closed
110	2022-04-01	2022-05-01	Training	Complete	45	Closed
120	2022-01-01	2022-04-01	Consult	Pending	15	Closed
120	2022-01-01	2022-04-01	SessionA	Complete	30	Closed
130	2022-05-01	2022-05-15	Consult	Complete	60	Closed
130	2022-05-01	2022-05-15	SessionB	Complete	30	Closed

I want to rearrange the data to capture all the info in one line per ID, like this:

ID	Intake_Date	Closed_Date	Outcome	Result	Outcome_time_spent	Outcome2	Result2	Outcome_time_spent2	Case Status
110	2022-04-01	2022-05-01	Consult	Individual	30	Training	Complete	45	Closed
120	2022-01-01	2022-04-01	Consult	Pending	15	SessionA	Complete	30	Closed
130	2022-05-01	2022-05-15	Consult	Complete	60	SessionB	Complete	30	Closed

Re: Transposing

ballardw — Wed, 05 Oct 2022 21:39:06 GMT

Do you want a data set, for further analysis, or a report for people to read?

If you think you want a data set, what exactly do you expect to do with the data in the "wide" format that cannot be done with the existing format.

Also for your consideration: You show 4 different values for Outcome (at least). Does it really make sense to have Outcome2 sometimes Training, sometimes Session A and sometimes Session B? That sort of makes the utility of that data structure a little questionable as you have to deal with multiple variables for anything involving Outcome, which can lead to pretty ugly and hard to follow code.

Re: Transposing

MCandun — Wed, 05 Oct 2022 21:56:38 GMT

Hi, thanks for your reply! I am looking to create a a dataset that will be merged (with 2 other datasets) by ID to create a complete line list for each case (ID). There are several outcomes possible and each entry (i.e., Outcome1 to Outcome8) could be any one of the outcomes.

Re: Transposing

ballardw — Wed, 05 Oct 2022 22:09:51 GMT

This format is not needed to "merge" data sets though the technique to use varies on the content of the other two data sets and the expected output.

So may examples of the other 2 data sets and the expected result.

Re: Transposing

Ksharp — Fri, 07 Oct 2022 11:20:56 GMT

data have;
input
(ID

Intake_Date

Closed_Date

Outcome

Result

Outcome_time_spent

CaseStatus
) ( : $40.);
cards;
110

2022-04-01

2022-05-01

Consult

Individual

30

Closed

110

2022-04-01

2022-05-01

Training

Complete

45

Closed

120

2022-01-01

2022-04-01

Consult

Pending

15

Closed

120

2022-01-01

2022-04-01

SessionA

Complete

30

Closed

130

2022-05-01

2022-05-15

Consult

Complete

60

Closed

130

2022-05-01

2022-05-15

SessionB

Complete

30

Closed

;
data temp;
 set have;
 by id Intake_Date Closed_Date CaseStatus;
 if first.CaseStatus then n=0;
 n+1;
run;
proc transpose data=temp out=temp2;
 by id Intake_Date Closed_Date CaseStatus n;
 var Outcome
Result
Outcome_time_spent
;
run;

proc transpose data=temp2 out=want(drop=_:);
 by id Intake_Date Closed_Date CaseStatus ;
 id _name_ n;
 var col1;
run;