sorting by within values of a variable

[hasnat]

Hi. Is there anyone out there who can help me how to further sort this data.

Data E1;
input KYGVKEY & FYYYYQ & EPSFIQ;
*Sample data given,

where KYGKEY=firm identifier, FYYYYQ=fiscal year and quarters, EPSFIQ= (an)earning per share;
CARDS;
1690 1980.0 0.06
1690 1980.1 0.06
1690 1980.3 0.07
1690 1980.4 0.14
1690 1981.0 0.08
1690 1981.1 0.09
1690 1981.3 0.11
1690 1981.4 0.04
1730 1980.0 0.10
1730 1980.1 0.15
1730 1980.3 0.08
1730 1980.4 0.22
1730 1981.0 1.08
1730 1981.1 0.19
1730 1981.3 1.11
1730 1981.4 0.04
;
RUN;

PROC PRINT DATA=E1;
RUN;
*Required is to sort/arrange this data by firm's identifier=KYGVKEY, and
cross-sectional quarters: 1st quarters from 1980 and 1981 comes first
and 2nd next and so on with corresponding EPSFIQ,
to compare EPSFIQ of each quarter with corresponding quarter of last year;

[FreelanceReinh · Accepted solution]

Hi @hasnat,

PROC SQL is quite good at sorting data in non-standard ways:

proc sql;
create table e1s as
select * from e1
order by kygvkey, scan(fyyyyq,2), scan(fyyyyq,1);
quit;

But in the long run it will be more convenient to split FYYYYQ into two separate variables for fiscal year and quarter, respectively.

[hasnat]

Hi thanks. the idea of dissecting the variable from the start was great as it had more than one million obs. I did that in excel and imported and everything worked well.