How do I sort columns in a SAS query

Accepted Solution Solved
Reply
Contributor
Posts: 59
Accepted Solution

How do I sort columns in a SAS query

Hello all

 

I have a data set that includes upwards to 50 columns and I want the data to be sorted based on three of those columns. I have tried the following but it doesnt seem to work. Any help would be appreciated.

 

PROC SQL;

CREATE Table Results as

Select *

from step2

WHERE MSG1 NOT IN ('HP_CC_EXACT_DUP','HP_DC_COB_EOP_MIS','HP_DC_COB_REC_INACTV','HP_DC_ILR_SUS_WC',

'HP_DC_PR_FL','HP_EXCL_OON_PROV','HP_ILR_SUS_Q_NO_RTN','HP_MAN_DENY_BILLED_INCORR','HP_MAN_DENY_DUPE',

'HP_MAN_DENY_MISMATCH_SERV','HP_MAN_DENY_VEND_AUDIT','HP_MAN_MEHN_PROV_DUPBILL','HP_MAN_NDM_PRV_MSSNG_MPIN_OR_NPI',

'HP_MAN_NOREF','HP_MAN_PAY_COOR_NOPAYPRIMARY','HP_MBRST_AUTH','HP_NDM_NOMATH_RATE_EXHIBIT','CLA-FL-BENS-018',

'HP_NDM_PRV_ACTV_AFT_END_EFF','HP_NDM_PRV_TERM_B4_STRT_EFF','HP_PRC_DU','HP_PRC_FQ','HP_PRC_QW','CLA-FL-PPRI-002',

'HP_PRC_VO','HP_PRC_ZT','CLA-FL-BENS-033','HP_COVG_NOCOVER_0002','HP_AUTH_NOTFOUND_NOB_OON','HP_COVG_NOCOVER_0003',

'HP_HARD_REF_EXCEED_NOB_IN','HP_HARD_REF_NOTFOUND_IN_HMO','HP_MAN_MSUP_NO_EOMB','HP_DC_ILR_OTR_CARRIER_MEM_LIAB',

'HP_MAN_NOREF','HP_MBRST_AUTH','HP_NDM_NOMATH_RATE_EXHIBIT','HP_MAN_DENY_COB_ROLLUP','HP_MAN_DENY_DATE_NOT_ON_EOB',

'HP_DC_ILR_SUS_MVA','HP_DC_PCP_CONSULT','HP_DC_REPROCESS_PENALTY','HP_LIM_UNIT_EXCEED','HP_MAN_DENY_APPEAL_FL_EXCEED',

'HP_MAN_DENY_NO_AUTH','HP_MAN_DENY_VEND_DUPE','HP_MAN_DENY_VEND_UPHOLD_DENIAL','HP_MAN_MA_AUTO_PRIME','HP_PRC_25',

'HP_PRC_42','HP_PRC_73','HP_PRC_73_NOT_CONTR','HP_PRC_CD','HP_PRC_H9','HP_PRC_PG','HP_PRC_PU','HP_REF_NOTFOUND_IN',

'HP_CD_ANCCLM_FACILITY','HP_CD_PRIMDENYTOANC_FACILITY','HP_DC_COB_DONE','HP_DC_COB_FL_NONNH','HP_DC_INVLD_BILL_TYPE',

'HP_DC_ME_FL','HP_DC_MED_COB_DONE','HP_LIM_COVDOL_EXCEED','HP_LIM_WHD_EXCEED','HP_LIM_WHD_MET','HP_LIM_WHD_METEXCEED',

'HP_MAN_CLAIM_REPLACED','HP_MAN_DENY_COBFL','HP_MAN_DENY_COBNOTACCEPT','HP_MAN_MBR_MISSING_INFO','HP_MAN_MSUP_PROV_DUPBILL',

'HP_MAN_PAY_APPEAL_REVERSED','HP_PRC_25_PROC','HP_PRC_BILATERAL_SPLIT_LINES','HP_PRC_EM','HP_PRC_EM_UNCL_SURGERY',

'HP_PRC_LATE_CHARGE','HP_PRC_MAN_ZERO_CLMLN_QTY','HP_PRC_MANUALLY_PRICED','HP_PRC_QS','HP_PRC_REPLACED','HP_PRC_ZERO_CLAIMLINE_QTY'

'CLA-FL-BENS-001','HP_CC_NPT_EXC','HP_CC_SUS_DUP','HP_DC_INVLD_PROC_REV_CD','HP_LIM_WHD_NOTMET','HP_MAN_PAY_VEND_AUDIT',

'HP_PRC_BILLED_UNITS','HP_PRC_M7','HP_PRC_T3','HP_PRC_TP_ICES_DENY','HP_PRC_TP_ICES_PAY','HP_PRC_TP_NON_ICES_DENY',

'HP_PRC_TP_NON_ICES_PAY','HP_REPLACEMENT_CLM_INVLD_DCN','XX')

order by memno, begdate, APPL_FULL_SVC_NBR;

quit;


Accepted Solutions
Solution
‎05-21-2018 02:43 PM
Super User
Posts: 13,542

Re: How do I sort columns in a SAS query

Posted in reply to wheddingsjr

@wheddingsjr wrote:

My apologies, please disreagrd the WHERE statement as it is irrelevevant to my question.

 

Thanks

 

PROC SQL;

CREATE Table Results as

Select *

from step2

order by MEMNO, BEGDATE, APPL_FULL_SVC_NBR;

quit;


Perhaps you can show a brief example of your data after the sort that is not "working" and explain the desired order.

 

One thing that is common for sort "problems" is having a value that looks numeric but is actually character, so provide descriptions such as from proc contents of those three variables you are attempting to sort with.

View solution in original post


All Replies
Contributor
Posts: 59

Re: How do I sort columns in a SAS query

Posted in reply to wheddingsjr

My apologies, please disreagrd the WHERE statement as it is irrelevevant to my question.

 

Thanks

 

PROC SQL;

CREATE Table Results as

Select *

from step2

order by MEMNO, BEGDATE, APPL_FULL_SVC_NBR;

quit;

Solution
‎05-21-2018 02:43 PM
Super User
Posts: 13,542

Re: How do I sort columns in a SAS query

Posted in reply to wheddingsjr

@wheddingsjr wrote:

My apologies, please disreagrd the WHERE statement as it is irrelevevant to my question.

 

Thanks

 

PROC SQL;

CREATE Table Results as

Select *

from step2

order by MEMNO, BEGDATE, APPL_FULL_SVC_NBR;

quit;


Perhaps you can show a brief example of your data after the sort that is not "working" and explain the desired order.

 

One thing that is common for sort "problems" is having a value that looks numeric but is actually character, so provide descriptions such as from proc contents of those three variables you are attempting to sort with.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 109 views
  • 0 likes
  • 2 in conversation