Sort results by groups (as final result)

[Riccardo Busin]

Good afternoon,
I would like to ask if this is possible in Stata.
I have the following data (input from excel file):

ID	CDS_COD	PDS_DES	MODULE_CODE	DES_TIPO_CICLO	LOANED	LOAN_FROM_MODULE_CODE	LOAN_GRADE	FATHER CODE ID
460041	LT10	PERCORSO COMUNE	LT1220	I Semestre			FATHER	460041
459951	LM3	MASTER EUROPEÉN EN ETUDES FRANÇAISES ET FRANCOPHONES	LMF05L	II Semestre	SI	LMF05L	SON	459795
459948	LM3	MASTER EUROPEÉN EN ETUDES FRANÇAISES ET FRANCOPHONES	LMF03L	II Semestre	SI	LMF03L	SON	459869
459925	LM3	ESTUDIOS IBÉRICOS E IBEROAMERICANOS	LMI02Q-1	I Semestre	SI	LMI02Q-1	SON	459847
459869	LM3	LETTERATURE E CULTURE	LMF03L	II Semestre			FATHER	459869
459847	LM3	LETTERATURE E CULTURE	LMI02Q-1	I Semestre			FATHER	459847
459795	LM3	LETTERATURE E CULTURE	LMF05L	II Semestre			FATHER	459795
459714	LM5	SCIENZE DEL LINGUAGGIO	LM005L	II Semestre	SI	LM005L	SON	459795
459704	LM5	SCIENZE DEL LINGUAGGIO	LM001Q	I Semestre	SI	LM001Q	SON	459847
459619	LM60	PERCORSO COMUNE	LM1970	I Semestre	SI	LM1970	SON	459847
412248	LM3	LETTERATURE E CULTURE	FM0068	II Semestre		FM0068	SON	444298
407036	LM3	ESTUDIOS IBÉRICOS E IBEROAMERICANOS	LMI980	I Semestre	SI	LMI980	SON	459847
407035	LM3	LETTERATURE E CULTURE	LMI980	I Semestre	SI	LMI980	SON	459847
459982	LM3	LETTERATURE E CULTURE	LMF04L	II Semestre	SI	LMF04L	SON	459869
459972	LM3	MASTER EUROPEÉN EN ETUDES FRANÇAISES ET FRANCOPHONES	LMF04L	II Semestre	SI	LMF04L	SON	459869

I would like this grouping as the final result (based on the ID and parent teaching):

ID	CDS_COD	PDS_DES	MODULE_CODE	DES_TIPO_CICLO	LOANED	LOAN_FROM_MODULE_CODE	LOAN_GRADE	FATHER CODE ID
459847	LM3	LETTERATURE E CULTURE	LMI02Q-1	I Semestre			FATHER	459847
459925	LM3	ESTUDIOS IBÉRICOS E IBEROAMERICANOS	LMI02Q-1	I Semestre	SI	LMI02Q-1	SON	459847
407035	LM3	LETTERATURE E CULTURE	LMI980	I Semestre	SI	LMI980	SON	459847
407036	LM3	ESTUDIOS IBÉRICOS E IBEROAMERICANOS	LMI980	I Semestre	SI	LMI980	SON	459847
459619	LM60	PERCORSO COMUNE	LM1970	I Semestre	SI	LM1970	SON	459847
459704	LM5	SCIENZE DEL LINGUAGGIO	LM001Q	I Semestre	SI	LM001Q	SON	459847
459795	LM3	LETTERATURE E CULTURE	LMF05L	II Semestre			FATHER	459795
459714	LM5	SCIENZE DEL LINGUAGGIO	LM005L	II Semestre	SI	LM005L	SON	459795
459951	LM3	MASTER EUROPEÉN EN ETUDES FRANÇAISES ET FRANCOPHONES	LMF05L	II Semestre	SI	LMF05L	SON	459795
460041	LT10	PERCORSO COMUNE	LT1220	I Semestre			FATHER	460041
459869	LM3	LETTERATURE E CULTURE	LMF03L	II Semestre			FATHER	459869
459972	LM3	MASTER EUROPEÉN EN ETUDES FRANÇAISES ET FRANCOPHONES	LMF04L	II Semestre	SI	LMF04L	SON	459869
459982	LM3	LETTERATURE E CULTURE	LMF04L	II Semestre	SI	LMF04L	SON	459869
459948	LM3	MASTER EUROPEÉN EN ETUDES FRANÇAISES ET FRANCOPHONES	LMF03L	II Semestre	SI	LMF03L	SON	459869
412248	LM3	LETTERATURE E CULTURE	FM0068	II Semestre			SON	444298

(in the example there are also cases of teaching fathers without children, and teaching children without fathers).

If it is possible (but it is not an essential request), color the line where the "father" teaching appears, red.
Again, I don't know if this is possible in Stata. For me it would be a great help (here I have only given a few examples, but in reality there are many more).

Thank you for your help!
Best regards
Riccardo

[Clyde Schechter]

Perhaps somebody else can understand what you want and will reply. You might want to wait the rest of the day to see if that happens. But I am completely baffled by the contrast between your description of what you want and the desired results you show. Looking at the desired results you show, there is no sorting of the data at all, except by des_tipo_ciclo. Other than that, things appear to be in arbitrary order on every variable. Evidently you perceive some order in this that I do not discern. If you can state clearly what that order is, there may be a way to replicate it in Stata. Also, although the data are clearly not being sorted on ID, contrary to your statement, it is not at all clear to me which variable(s) would identify parent teaching to sort on. So that, too, would need clarification.

There is one part of your question I can answer quickly. It is not possible to apply colors to data in Stata. It is true that in the data viewer string variables are shown in a brownish/red color--this is to distinguish them from numeric variables with a value label, which look like strings to the human eye, and are shown in blue. But this applies to the variables only, not to the entire observation (row).

When you post back, it would also be helpful if you posted your example data from your Stata data set using the -dataex- command. The table you show for what you have is irregularly constructed and requires "surgery" to get it into Stata through copy/paste. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[daniel klein]

Seems to me like you want to have fathers and sons with the same ID sorted in groups. I cannot tell what the variable names are form what you show but you want to sort by the last variable and the the second last variable.

[Clyde Schechter]

Perhaps that is what O.P. wants. But if so why does Father 460041 precede Father 459869? Perhaps it has to do with the semester changing from I to II? Would it be -sort des_tipo_cicolo loan_grade fathercodeid-.

[Riccardo Busin]

Dear Clyde and Daniel,
Thanks for your help and I apologize if I caused any confusion.

In the first scheme I inserted (input from excel file) the IDs are not inserted in numerical order (I could have used the sort command, so there was less confusion).

In the second scheme (the one where I marked the first lines in red), it is the output I would like to obtain using the previous scheme. I would like the "father" teaching to come first, followed by the "sons". Both the "father" and the "children" share the FATHER CODE ID. To understand if they are "children" it is necessary that the FATHER CODE ID is equal to the "father" ID as in this example (which I take from the previous one).

ID	CDS_COD	PDS_DES	MODULE_CODE	DES_TIPO_CICLO	LOANED	LOAN_FROM_MODULE_CODE	LOAN_GRADE	FATHER CODE ID
459847	LM3	LETTERATURE E CULTURE	LMI02Q-1	I Semestre			FATHER	459847
459925	LM3	ESTUDIOS IBÉRICOS E IBEROAMERICANOS	LMI02Q-1	I Semestre	SI	LMI02Q-1	SON	459847
407035	LM3	LETTERATURE E CULTURE	LMI980	I Semestre	SI	LMI980	SON	459847
407036	LM3	ESTUDIOS IBÉRICOS E IBEROAMERICANOS	LMI980	I Semestre	SI	LMI980	SON	459847
459619	LM60	PERCORSO COMUNE	LM1970	I Semestre	SI	LM1970	SON	459847
459704	LM5	SCIENZE DEL LINGUAGGIO	LM001Q	I Semestre	SI	LM001Q	SON	459847

I hope I have been clearer and I apologize for the time I have caused you to waste due to my lack of clarity.

Thank you again

[Clyde Schechter]

That's a bit clearer. But it clearly does not describe the desired arrangement you showed in #1 (see counterexample mentioned in #4). Have you tried the suggestions in #4 or #5? Did either one do what you asked?

[Riccardo Busin]

Dear Clyde,
Thank you for taking the time to resolve my problem.

As Daniel says, I would like to have fathers and sons with the same "FATHER CODE ID" sorted into groups.

The first table I inserted (in point #1) is the Excel input file. I would like to order it as I explained in point #5.

The only idea I have right now is to try a sort on "FATHER CODE ID". The "father" and "son" teachings share the same "FATHER CODE ID" so, in theory, they should be sorted (I would just need the "father" to appear first in the "LOAN_GRADE" column for each group).

I hope I was able to explain myself and sorry for the confusion, I'm not yet expert with Stata.

Thanks again for your help and patience.

[Clyde Schechter]

The only idea I have right now is to try a sort on "FATHER CODE ID".

It will sort the data on father code id, but it will not put the father observations before the son observations.
In #4, Daniel Klein suggested:

sort loan_grade fathercodeid

That will do what you say you want to do. But the results will not look like your second table in #1.

IN #5, I suggested

sort des_tipo_cicolo loan_grade fathercodeid

That will reproduce what you show in your second table in #1, but it is not what you say you want to do.

In any case, you should try both of these and see which one, if either, does what you are looking for. If neither of these solutions works, you will have to post back with a better explanation of what you want. And again, I can't emphasize enough that it is important to post examples from your Stata data set, not from an Excel spreadsheet. If you have not yet imported the data into Stata, it is premature to be asking for Stata help, except for help with the importation itself. And, again, when posting example Stata data, the most helpful way to do it is with the -dataex- command. How to do that I explained in #2.

[Riccardo Busin]

Dear Clyde,
Thank you once again for your help and patience.
I confirm that with the suggested codes I now get the result I wanted.

I also thank you for teaching me how to use the dataex command.

I hope to increase my skills in Stata more and more.

Thanks again for everything.
Best regards
Riccardo