topic ERROR: BY variables are not properly sorted on data set WORK.dataset in SAS Programming

ERROR: BY variables are not properly sorted on data set WORK.dataset

LauraBona92 — Tue, 30 May 2023 10:05:19 GMT

Hello,

I try this code but I get an error in my data step (cfr Code and Log).

Can someone tell me what I am doing wrong here ?

Many thanks !

Laura

PROC SORT DATA=NSIHHCDW.TU_NSIH_HOSP(where=(dt_valid_to='31DEC9999:00:00:00'dt)) OUT=nsih_hosp;
  BY idc_hosp_nihdi cd_hosp_site_tpe;
RUN;

DATA nsih_hosp;
  SET nsih_hosp(where=(dt_end_site is null));
  BY idc_hosp_nihdi ; *cd_hosp_site_tpe;
  IF first.idc_hosp_nihdi THEN OUTPUT ;
RUN;




                                                          The SAS System
1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='test code DWH EXP EXT';
4          %LET _CLIENTPROCESSFLOWNAME='BeH-SAC';
5          %LET _CLIENTPROJECTPATH='\\Client\M$\S-SPECIFIC\BEHSAC\E. Analyses\SAS_projects\eguide_proj\BeH-SAC 2021_analysis6.egp';
6          %LET _CLIENTPROJECTPATHHOST='WIV-P-XAW02';
7          %LET _CLIENTPROJECTNAME='BeH-SAC 2021_analysis6.egp';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGPDF TEMP;
15         ODS PDF(ID=EGPDF) FILE=EGPDF STYLE=Pearl;
NOTE: Writing ODS PDF(EGPDF) output to DISK destination "EGPDF", printer "PDF".
16         FILENAME EGSR TEMP;
17         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
18             STYLE=Pearl
19             STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SASHome/x86/SASEnterpriseGuide/7.1/Styles/Pearl.css")
20             NOGTITLE
21             NOGFOOTNOTE
22             GPATH=&sasworklocation
SYMBOLGEN:  Macro variable SASWORKLOCATION resolves to "E:\Work\WorkApp\Lev3\SASAppRES\_TD9332_WIV-I-SAS03_\Prc2/"
23             ENCODING=UTF8
24             options(rolap="on")
25         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
26         FILENAME EGXLSSX TEMP;
27         ODS EXCEL(ID=EGXLSSX) FILE=EGXLSSX STYLE=Excel
28         OPTIONS (
29          EMBEDDED_TITLES="no" EMBEDDED_FOOTNOTES="no"
30         );
31         
32         GOPTIONS ACCESSIBLE;
33         PROC SORT DATA=NSIHHCDW.TU_NSIH_HOSP(where=(dt_valid_to='31DEC9999:00:00:00'dt)) OUT=nsih_hosp;
34         BY idc_hosp_nihdi cd_hosp_site_tpe;
35         run;
NOTE: Sorting was performed by the data source.
NOTE: There were 339 observations read from the data set NSIHHCDW.TU_NSIH_HOSP.
      WHERE dt_valid_to='  31DEC9999:00:00:00'DT;NOTE: The data set WORK.NSIH_HOSP has 339 observations and 28 variables.
NOTE: Compressing data set WORK.NSIH_HOSP decreased size by 0.00 percent. 
      Compressed is 3 pages; un-compressed would require 3 pages.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds
      

36         
37         DATA nsih_hosp;
38           SET nsih_hosp(where=(dt_end_site is null));
39           BY idc_hosp_nihdi ; *cd_hosp_site_tpe;
40           IF first.idc_hosp_nihdi THEN OUTPUT ;
41         RUN;

ERROR: BY variables are not properly sorted on data set WORK.NSIH_HOSP.ID_TU_NSIH_HOSP=27901067 IDC_HOSP_FUSN_AGREM=998 IDC_HOSP_SITE=1201 IDC_HOSP_NIHDI=72099803 IDC_HOSP_NSIH=1104 CD_HOSP_SITE_TPE=SP
TX_HOSP_NAM=ZIEKENHUISNETWERK ANTWERPEN TX_HOSP_SITE_NAM=PZ STUIVENBERG TX_ADR=POTHOEKSTRAAT 109 CD_POSTAL_CODE=2060
TX_CITY=ANTWERPEN CD_REGIO=V TX_PROVINCE=Antwerpen TX_HOSP_TPE_FUSN=specialised TX_HOSP_TPE_SITE=specialised
TX_HOSP_TPE_NIHDI=specialised TX_HOSP_TCHNG_FUSN=non-teaching TX_HOSP_TCHNG_SITE=non-teaching TX_HOSP_TCHNG_NIHDI=non-teaching
TX_HOSP_SIZE_FUSN=small TX_HOSP_SIZE_SITE=small TX_HOSP_SIZE_NIHDI=small TX_HOSP_KIND=psychiatric
DT_VALID_FROM=26FEB2021:10:46:45.377000 DT_VALID_TO=31DEC9999:00:00:00.000000 IDC_HOSP_NIHDI_NEW=  DT_START_SITE=01JAN1960
DT_END_SITE=. FIRST.IDC_HOSP_NIHDI=1 LAST.IDC_HOSP_NIHDI=1 _ERROR_=1 _N_=289
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 290 observations read from the data set WORK.NSIH_HOSP.
      WHERE dt_end_site is null;
WARNING: The data set WORK.NSIH_HOSP may be incomplete.  When this step was stopped there were 180 observations and 28 variables.
NOTE: Compressing data set WORK.NSIH_HOSP decreased size by 0.00 percent. 
      Compressed is 2 pages; un-compressed would require 2 pages.
WARNING: Data set WORK.NSIH_HOSP was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds
      

42         
43         GOPTIONS NOACCESSIBLE;
44         %LET _CLIENTTASKLABEL=;
45         %LET _CLIENTPROCESSFLOWNAME=;
46         %LET _CLIENTPROJECTPATH=;
47         %LET _CLIENTPROJECTPATHHOST=;
48         %LET _CLIENTPROJECTNAME=;
49         %LET _SASPROGRAMFILE=;
50         %LET _SASPROGRAMFILEHOST=;
51         
52         ;*';*";*/;quit;run;
53         ODS _ALL_ CLOSE;
NOTE: ODS PDF(EGPDF) printed no output. 
      (This sometimes results from failing to place a RUN statement before the ODS PDF(EGPDF) CLOSE statement.)
NOTE: Writing EXCEL(EGXLSSX) file: E:\Work\WorkApp\Lev3\SASAppRES\_TD9332_WIV-I-SAS03_\#LN01256
54         
55         
56         QUIT; RUN;
.

how to fix ERROR: BY variables are not properly sorted on data set WORK.dataset

LauraBona92 — Tue, 30 May 2023 12:19:29 GMT

Hello,

the following data step isn't working, while I have sorted the dataset above.

Can someone help me out?

PROC SORT DATA=NSIHHCDW.TU_NSIH_HOSP(where=(dt_valid_to='31DEC9999:00:00:00'dt)) OUT=nsih_hosp;
BY idc_hosp_nihdi cd_hosp_site_tpe;
run;

DATA nsih_hosp;
  SET nsih_hosp(where=(dt_end_site is null));
  BY idc_hosp_nihdi ; *cd_hosp_site_tpe;
  IF first.idc_hosp_nihdi THEN OUTPUT ;
RUN;

ERROR: BY variables are not properly sorted on data set WORK.NSIH_HOSP.

Re: how to fix ERROR: BY variables are not properly sorted on data set WORK.dataset

A_Kh — Tue, 30 May 2023 13:13:45 GMT

I guess in your SET statement WHERE dataset option affecting the previously sorted order.

SET nsih_hosp(where=(dt_end_site is null));

Try to apply this where dataset option in your previous step.

Re: ERROR: BY variables are not properly sorted on data set WORK.dataset

Reeza — Wed, 31 May 2023 20:52:38 GMT

Your log is stating that sorting is done by the host. And it looks like you're in Europe so I wonder if languages are being set differently. I would copy the data set down, sort locally and then see what happens. The following should generate the desired results.

NULL can also be treated differently in DB than in SAS, so using MISSING() may help.

data nsih_hosp;
set NSIHHCDW.TU_NSIH_HOSP;
where dt_valid_to='31DEC9999:00:00:00'dt;
run;

PROC SORT DATA=nsih_hosp;
  BY idc_hosp_nihdi cd_hosp_site_tpe;
RUN;

*selects the first record from previous sort;
PROC SORT DATA=nsih_hosp(where=(missing(dt_end_site))) out=nsih_hosp2 NODUPKEY;
  BY idc_hosp_nihdi ;
RUN;

FYI - is anything in the log there private that should be deleted?

Re: ERROR: BY variables are not properly sorted on data set WORK.dataset

Tom — Wed, 31 May 2023 23:06:52 GMT

Try to find the issue yourself. Look for the first place where the value goes DOWN.

DATA _null_;
  SET nsih_hosp(where=(dt_end_site is null));
  lag_id = lag(idc_hosp_nihdi);
  if idc_hosp_nihdi < lag_id then do;
      put (_n_ lag_id idc_hosp_nihdi ) (=);
      stop;
  end;
  format _all_;
RUN;

Re: how to fix ERROR: BY variables are not properly sorted on data set WORK.dataset

donricardo — Thu, 01 Jun 2023 19:50:31 GMT

Wow, that's a strange one. Try removing the SET statement where option, and place in into the PROC SORT where option as a compound filter. Next item: you really don't need the OUTPUT statement since the run statement will handle that automatically.

Re: how to fix ERROR: BY variables are not properly sorted on data set WORK.dataset

Kurt_Bremser — Thu, 01 Jun 2023 21:11:24 GMT

Please do not post the same question multiple times.