BookmarkSubscribeRSS Feed
LauraBona92
Calcite | Level 5

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; .

 

Spoiler
Spoiler
 

 

 

6 REPLIES 6
Reeza
Super User

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?

 

Tom
Super User Tom
Super User

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;
LauraBona92
Calcite | Level 5

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.

 

 

A_Kh
Lapis Lazuli | Level 10

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.  

donricardo
SAS Employee

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.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 772 views
  • 3 likes
  • 6 in conversation