From wide to long

[NLIL]

Hello everyone!

I am hoping for your help today - I struggeled all day yesterday!

I want to go from long to wide in my dataset in SAS 9.4. The dataset has these variables;

ID, dateIn, dateOut, JournalNo, MainDiag, Diag2, Diag3.

Some ID's have many diagnoses from different dates or the same dates, and some are the same, some are not. So some ID's are allready in the dataset many times. I've sorted the dataset so there is no dublicate rows with the same ID and diagnose, keeping the oldest one. I did that this way:

proc sort data=mydata;
by ID_ descending DateIn;
run;
proc sort data=mydata nodupkey;
by ID_ MainDiag Diag1 Diag2 Diag3
run;

Hope that is correct?

I need this dataset to be long instead of wide, regarding  the diagnoses

So, I want all diagnoses to list under eachother and end up with only one variable containing the diagnoses. I don't care if they were the main diagnose or not.

I have tried the transpose function in 1000 different ways, but it is just not working out for me.

I hope this is enough info for getting help

Looking forward to hearing from someone.

Regards,

Ninna

[PaigeMiller]

Show us a portion of your data, following these instructions (do not skip this step):

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

[ed_sas_member]

Hi @NLIL,

I agree with @PaigeMiller, it would be easier if you could share a portion of your data to better understand what problem you're trying to solve.

I have some doubt about you approach to select the oldest diagnosis in case you have more than one record for an ID -> with a "descending" proc sort, if think you're doing the opposite.

Here is maybe something you can try, but I am not pretty sure this is what you want.

/* if same ID and diagnose, keep the oldest one */

proc sort data=mydata;
	by ID_ Diag1 Diag2 Diag3 DateIn;
run;

data mydata2;
	set mydata;
	by ID_ Diag1 Diag2 Diag3;
	if first.diag3;
run;

/* transform dataset as a long one instead of wide, regarding  the diagnoses */

proc transpose data=mydata2 out=mydata_tr (drop=_name_);
	var MainDiag Diag1 Diag2 Diag3;
	id id_;
	by notsorted DateIn ;
run;

[ballardw]

---

@NLIL wrote:

Hello everyone!

I am hoping for your help today - I struggeled all day yesterday!

I want to go from long to wide in my dataset in SAS 9.4. The dataset has these variables;

ID, dateIn, dateOut, JournalNo, MainDiag, Diag2, Diag3.

Some ID's have many diagnoses from different dates or the same dates, and some are the same, some are not. So some ID's are allready in the dataset many times. I've sorted the dataset so there is no dublicate rows with the same ID and diagnose, keeping the oldest one. I did that this way:

proc sort data=mydata;
by ID_ descending DateIn;
run;
proc sort data=mydata nodupkey;
by ID_ MainDiag Diag1 Diag2 Diag3
run;

Hope that is correct?

I need this dataset to be long instead of wide, regarding  the diagnoses

So, I want all diagnoses to list under eachother and end up with only one variable containing the diagnoses. I don't care if they were the main diagnose or not.

I have tried the transpose function in 1000 different ways, but it is just not working out for me.

I hope this is enough info for getting help

Looking forward to hearing from someone.

Regards,

Ninna

---

One suspects you did not try the transpose until after trying to remove duplicates. I am guessing that the transpose may need to be the first step after reading your data. So an example of that data, or as a minimum the variable names and types (proc contents) and what grouping variables are needed to be kept would be one place to start.

Please note that transpose doesn't perform well with a mix of character and numeric variables on the VAR statement. So make sure that all of your "diagnosis" variables are the same type.