BookmarkSubscribeRSS Feed
NLIL
Calcite | Level 5

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

3 REPLIES 3
PaigeMiller
Diamond | Level 26

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...

 

 

 

--
Paige Miller
ed_sas_member
Meteorite | Level 14

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
Super User

@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.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 577 views
  • 0 likes
  • 4 in conversation