ERROR: BY VARIABLES ARE NOT PROPERLY SORTED ON DATA SET WORK.PROJ3. ??

Reply
Frequent Contributor
Posts: 128

ERROR: BY VARIABLES ARE NOT PROPERLY SORTED ON DATA SET WORK.PROJ3. ??

3                                                          THE SAS SYSTEM       
                                                                                
31                                                                              
32               DATA PROJ3;                                                    
33                 INFILE PROJECT3 DLM=',' DSD FIRSTOBS=2;                      
34                  INPUT FINARR   $2.                                          
35                       PROCESSTYPE $5.                                        
36                       TAXID       14                                         
37                       PSO         $1.                                        
38                       DEBTORNM    $35.                                       
39                       RCNNUM      $11.                                       
40                       CLMBASEID   $9.;                                       
41               /*INPUT FINARR $ PROCESSTYPE $ TAXID $ PSO $                   
42                       DEBTORNM $ RCNNUM $ CLMBASEID $;*/                     
43               RUN;                                                           
                                                                                
NOTE: THE INFILE PROJECT3 IS:                                                   
      DSNAME=aaaaaaa.PROJECT3.CSV,                                              
      UNIT=3390,VOLUME=TST304,DISP=SHR,BLKSIZE=27998,                           
      LRECL=256,RECFM=VB                                                        NOTE: LOST CARD.                                                                
FINARR=NR PROCESSTYPE=,NR1, TAXID=0 PSO=0 DEBTORNM=0000,P,HHHH HHHHHH HOSPITAL
_N_=10815                                                                       
NOTE: 12576 RECORDS WERE READ FROM THE INFILE PROJECT3.                         
      THE MINIMUM RECORD LENGTH WAS 45.                                         
      THE MAXIMUM RECORD LENGTH WAS 75.                                         
NOTE: SAS WENT TO A NEW LINE WHEN INPUT STATEMENT REACHED PAST THE END OF A LINE
NOTE: THE DATA SET WORK.PROJ3 HAS 10814 OBSERVATIONS AND 7 VARIABLES.           
NOTE: THE DATA STATEMENT USED 0.02 CPU SECONDS AND 14531K.                      
                                                                                
NOTE: THE ADDRESS SPACE HAS USED A MAXIMUM OF 636K BELOW THE LINE AND 14968K ABO
                                                                                
                                                                                
44                                                                              
45              DATA PROJECT.DATA;                                              
46                MERGE PROJ1 PROJ2 PROJ3;                                      
47                BY CLMBASEID;                                                 
48                RUN;                                                          
                                                                                
ERROR: BY VARIABLES ARE NOT PROPERLY SORTED ON DATA SET WORK.PROJ3.             
ERROR: BY VARIABLES ARE NOT PROPERLY SORTED ON DATA SET WORK.PROJ3.             
ERROR: BY VARIABLES ARE NOT PROPERLY SORTED ON DATA SET WORK.PROJ3.             
FINARR=NR PROCESSTYPE=,NR21 TAXID=3 PSO=6 DEBTORNM=22,P,EXCEPTIONAL KIDZ REHABIL
FIRST.CLMBASEID=1 LAST.CLMBASEID=1 _ERROR_=1 _N_=118                            
NOTE: THE SAS SYSTEM STOPPED PROCESSING THIS STEP BECAUSE OF ERRORS.            
NOTE: SAS SET OPTION OBS=0 AND WILL CONTINUE TO CHECK STATEMENTS. THIS MAY CAUSE
NOTE: THERE WERE 2 OBSERVATIONS READ FROM THE DATA SET WORK.PROJ1.              
NOTE: THERE WERE 114 OBSERVATIONS READ FROM THE DATA SET WORK.PROJ2.            NOTE: THERE WERE 5 OBSERVATIONS READ FROM THE DATA SET WORK.PROJ3.              
WARNING: THE DATA SET PROJECT.DATA MAY BE INCOMPLETE.  WHEN THIS STEP WAS STOPPE
WARNING: DATA SET PROJECT.DATA WAS NOT REPLACED BECAUSE THIS STEP WAS STOPPED.  
NOTE: THE DATA STATEMENT USED 0.01 CPU SECONDS AND 15299K.                      
                                                                                
NOTE: THE ADDRESS SPACE HAS USED A MAXIMUM OF 636K BELOW THE LINE AND 15736K ABO
                                                                                
                                                                                
                                                                                
49                                                                              
50              PROC PRINT DATA=PROJECT.DATA;                                   
51                 TITLE 'IMPORT RECOUPEMENT PROJECT';                          
4                                                          THE SAS SYSTEM       
                                                                                
52              **NOTE - S/H 55744 DATA LINES**                                 
53                                                                              
54                                                                              
55          /*******************************************************************
NOTE: THE PROCEDURE PRINT USED 0.01 CPU SECONDS AND 14674K.                     
                                                                                
NOTE: THE ADDRESS SPACE HAS USED A MAXIMUM OF 636K BELOW THE LINE AND 15736K ABO
                                                                                
                                                                                
ERROR: ERRORS PRINTED ON PAGE 3.                                                
ERROR: ERRORS PRINTED ON PAGE 3.                                                
ERROR: ERRORS PRINTED ON PAGE 3.                                                
PROC Star
Posts: 8,164

Re: ERROR: BY VARIABLES ARE NOT PROPERLY SORTED ON DATA SET WORK.PROJ3. ??

I think the error message was pretty clear! You have to sort the three files before you can use a by group to merge them.

 

However, from the note you got from creating proj3, I'd suggest looking at the files before you do the merge. You may, minimally, have to add an option like truncover, to correctly input the file(s).

 

Art, CEO, AnalystFinder.com

 

Frequent Contributor
Posts: 128

Re: ERROR: BY VARIABLES ARE NOT PROPERLY SORTED ON DATA SET WORK.PROJ3. ??

I sorted each of the files by CLMBASEID but still getting errors.  Not sure what to do at this point.

6                DATA PROJ1;                                                    
7                  INFILE PROJECT1 DLM=',' DSD FIRSTOBS=2;                      
8                 INPUT  CLMBASEID   $9.                                        
2                                                          THE SAS SYSTEM       
                                                                                
9                        FINARR      $2.                                        
10                       PROCESSTYPE $5.                                        
11                       TAXID       14                                         
12                       PSO         $1.                                        
13                       DEBTORNM    $35.                                       
14                       RCNNUM      $11.;                                      
15                                                                              
16             /*INPUT FINARR $ PROCESSTYPE $ TAXID $ PSO $                     
17                         DEBTORNM $ RCNNUM $ CLMBASEID $*/                    
18                 RUN;                                                         
                                                                                
NOTE: THE INFILE PROJECT1 IS:                                                   
      DSNAME=AAAAAAA.PROJECT1.CSV,                                              
      UNIT=3390,VOLUME=TST303,DISP=SHR,BLKSIZE=27998,                           
      LRECL=256,RECFM=VB                                                        
                                                                                
NOTE: INVALID DATA FOR TAXID IN LINE 2 14-14.                                   
RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7
2         112209202,NR,NR1,270441273,P,NEW JERSEY HYPERBARIC,55555555555 62     
CLMBASEID=111111111 FINARR=,N PROCESSTYPE=R,NR1 TAXID=. PSO=R DEBTORNM=1,2222222
_ERROR_=1 _N_=1                                                                 
NOTE: INVALID DATA FOR TAXID IN LINE 3 14-14.                                   
Super User
Posts: 6,774

Re: ERROR: BY VARIABLES ARE NOT PROPERLY SORTED ON DATA SET WORK.PROJ3. ??

The first question is this.  Are you willing to spend the time and effort to learn the very basics of an INPUT statement?

 

Any answer you give is acceptable.  But it might color who will reply, and whether you can rely on your final program to get the right answer.

 

Here are a couple of questions to consider, a bit more detailed:

 

What does this INPUT statement do?

 

input TAXID 14;

 

Why in your sample program does SAS claim that PROCESSTYPE has a value of "R,NR1" ?

 

These are questions that you must be able to answer (whether your program generates errors or not), if you want to rely on the results being correct.

Super User
Posts: 10,239

Re: ERROR: BY VARIABLES ARE NOT PROPERLY SORTED ON DATA SET WORK.PROJ3. ??

Formatted input overrides the delimiters.

Assign informats in a separate statement and use list input, or use colon modifiers for the formats in the input statement.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
SAS Super FREQ
Posts: 824

Re: ERROR: BY VARIABLES ARE NOT PROPERLY SORTED ON DATA SET WORK.PROJ3. ??

As soon as you use delimited input, then i recommend to use the following INPUT statement

 

input
var1 : informat.
var2 : informat.
...
;

Using the : between varname and informat ensures that the informat is reading the values between the delimiter

Frequent Contributor
Posts: 128

Re: ERROR: BY VARIABLES ARE NOT PROPERLY SORTED ON DATA SET WORK.PROJ3. ??

Posted in reply to Bruno_SAS

ok, my file is a clean excel file so the data is not messy at all.  I'm wondering if I should even be doing it this way.  I cant do an import because it's not pc sas.  i've tried every way can and still not getting anywhere.  If I'm pulling from a clean data file, how would the input statement look?

 

 

 

 

 

 

Super User
Posts: 10,239

Re: ERROR: BY VARIABLES ARE NOT PROPERLY SORTED ON DATA SET WORK.PROJ3. ??

Post a few example lines of the csv, either as attachment or using the {i} button, and we can come up with suggestions.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 128

Re: ERROR: BY VARIABLES ARE NOT PROPERLY SORTED ON DATA SET WORK.PROJ3. ??

Posted in reply to KurtBremser
CLMBASEID	FINARR	PROCESSTYPE	TAXID	PSO	DEBTORNM	RCNNUM
111111111	NR	NR1	270000000	P	nnn jjjjjj hhhhhhhhhh	56789056434
111111111	NR	NR5	270000000	P	kkkkkkk mmmmmmm aaaaaaaaaa	56789056434
111111111	NR	NR1	270000000	P	nnn jjjjjj hhhhhhhhhhh	56789056434
111111111	NR	NR21	270000000	P	cccccccccc I aaaaaaaa MD PC	56789056434
111111111	NR	NR21	270000000	P	cccccccccc I aaaaaaaa MD PC	56789056434
Super User
Posts: 10,239

Re: ERROR: BY VARIABLES ARE NOT PROPERLY SORTED ON DATA SET WORK.PROJ3. ??

This file is tab-delimited, not comma-delimited (what a csv should be).

Read it like that:

data want;
infile cards dlm='09'x firstobs=2;
input
  CLMBASEID :$9.
  FINARR :$2.
  PROCESSTYPE :$5.
  TAXID :14.
  PSO :$1.
  DEBTORNM :$35.
  RCNNUM :$11.
;
cards;
CLMBASEID	FINARR	PROCESSTYPE	TAXID	PSO	DEBTORNM	RCNNUM
111111111	NR	NR1	270000000	P	nnn jjjjjj hhhhhhhhhh	56789056434
111111111	NR	NR5	270000000	P	kkkkkkk mmmmmmm aaaaaaaaaa	56789056434
111111111	NR	NR1	270000000	P	nnn jjjjjj hhhhhhhhhhh	56789056434
111111111	NR	NR21	270000000	P	cccccccccc I aaaaaaaa MD PC	56789056434
111111111	NR	NR21	270000000	P	cccccccccc I aaaaaaaa MD PC	56789056434
;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Ask a Question
Discussion stats
  • 9 replies
  • 187 views
  • 4 likes
  • 5 in conversation