BY Variables not sorted properly

MILKYLOVE — Tue, 26 Jul 2022 08:27:29 GMT

Hello guys,

I am trying to do the below extraction where I have a file named t_extract_fichier_5a at the end.

However I am getting errors at the last step where I am creating the file t_extract_fichier_5a.

The error I am getting is:

ERROR: BY variables are not properly sorted on data set work.t2a_extract_email

SOURCE=DWHC NU_ODS_PER_ROLE=2342008 x=Email EMAIL=&LT;ASSISTANTE@ALSACEMICRO.FR&GT; WK_Blacklist=. FIRST.SOURCE=1 LAST.SOURCE=1

FIRST.NU_ODS_PER_ROLE=1 LAST.NU_ODS_PER_ROLE=1 _ERROR_=1 _N_=2

My code:

*** paths not included ***

proc sql;
          create table personne_email as
                select distinct SOURCE
                     , NU_ODS_PER_ROLE
                     , EMAIL
                     , NU_TEL_MOB_HPERP
                from client.personne 
                     where TOP_CLT_ACTIF = 1
                          and CD_TYP_PP_PM_HPER = 'M' 
                          and TOP_CLT_DCD = 0
                     order by SOURCE, NU_ODS_PER_ROLE;
     quit;

     proc import datafile= "&chemin_bdc./bdc_coordonnees_invalides.xlsx"
          out=bdc_coord_inval
          dbms=xlsx replace;
          sheet="Coordonnées KO";
     run;

     proc sql;
          create table bdc_coord_inval_email as 
                select upcase(VALEUR2) as EMAIL, 0 as WK_Blacklist
                     from bdc_coord_inval
                          where CD_TYP eq "EMAIL" and SOURCE_DEPOT eq "BKL"
                               order by 1;
     quit;

     proc sort data=work.personne_email nodupkey;
          by EMAIL;
     run;

     proc sort data= work.bdc_coord_inval_email nodupkey;
          by EMAIL;
     run;


     proc sql;
          create table work.t2a_extract_email as
                select  a.SOURCE , a.NU_ODS_PER_ROLE, a.EMAIL, b.wk_blacklist
                     from work.personne_email as a
                          left join  work.bdc_coord_inval_email  as b
                               on a.EMAIL = b.EMAIL;
     quit;

     Data t_extract_fichier5a;
          retain SOURCE NU_ODS_PER_ROLE x EMAIL WK_Blacklist;
          set t2a_extract_email (keep= SOURCE NU_ODS_PER_ROLE EMAIL WK_Blacklist);
          x = "Email";
          by SOURCE NU_ODS_PER_ROLE ;
          run;

I can't seem to understand where the issue is coming. Please help.

Re: BY Variables not sorted properly

FreelanceReinh — Tue, 26 Jul 2022 08:52:55 GMT

Hello @MILKYLOVE,

Add the appropriate ORDER BY clause to the PROC SQL step creating work.t2a_extract_email:

order by SOURCE, NU_ODS_PER_ROLE;

as you did in your first PROC SQL step. Or, shorter and similar to your second PROC SQL step:

order by 1, 2;

topic Re: BY Variables not sorted properly in SAS Programming

BY Variables not sorted properly

Re: BY Variables not sorted properly