DATA Step, Macro, Functions and more

Ho to sort the data set as per reporting financial year and month

Reply
Contributor
Posts: 25

Ho to sort the data set as per reporting financial year and month

I have a below datset contains the variables Case_opened_month,Average_days.How will I get the below desired output

Dataset : A
=============

Case_opened_month       Average_days

APR17                                  10
AUG16                                  20
DEC16                                  30
FEB17                                 4.6
JAN17                                 30
JUL16                                  0
JUN17                                 0
MAR17                               0
MAY17                               40
NOV16                               25
OCT16                              15
SEP16                              35

 

 

Desired Output dataset A
-------------------------------

Case_opened_month       Average_days

JUL16                                  0
AUG16                                20
SEP16                                 35
OCT16                               15
NOV16                              25
DEC16                             30
JAN17                             30
FEB17                           4.6
MAR17                           0
APR17                          10
MAY17                          40
JUN17                           0

 

 

 

Regards,

chandu

Super User
Posts: 11,343

Re: Ho to sort the data set as per reporting financial year and month

Almost anything involving dates works much better if you create a SAS date valued variable. Then sorting is actually by date order plus there are many functions and formats to manipulate the data.

 

The following code creates a SAS date valued variable you could use to sort the data.

data have;
   set want;
   SASdate = input(case_opened_month,MONYY5.);
   format SASdate  MONYY5.;
run;

proc sort data = want;
   by SASdate;
run;

Note the SASDATE variable created this way will assume a day of the month of 1 for all of the values.

 

If you use a different FORMAT for SASDATE you can change appearance such as display 04/01/2017, 01APR17 or 01APR2017 or even calendar quarters without adding new variables. The formats can also be used to group values for analysis or summary.

Ask a Question
Discussion stats
  • 1 reply
  • 69 views
  • 0 likes
  • 2 in conversation