turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Error: BY variables are not properly sorted

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-27-2017 03:20 PM - last edited on 01-27-2017 03:24 PM by ChrisHemedinger

Hi I am running a procedure and results in to some errors (refer to following log). I dont have much knowledge about this program. Will one of the community member be able to help me. Let me know if you need furhter details. Thank you in advance.

3321 PROC IMPORT OUT= WORK.TEST1

3322 DATAFILE= "C:\Users\devendrashah\Desktop\for SAS.xlsm"

3323 DBMS=EXCEL REPLACE;

3324 RANGE="Test$";

3325 GETNAMES=YES;

3326 MIXED=NO;

3327 SCANTEXT=YES;

3328 USEDATE=YES;

3329 SCANTIME=YES;

3330 RUN;

NOTE: WORK.TEST1 data set was successfully created.

NOTE: The data set WORK.TEST1 has 53 observations and 6 variables.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.20 seconds

cpu time 0.04 seconds

3331 PROC IMPORT OUT= WORK.ref1

3332 DATAFILE= "C:\Users\devendrashah\Desktop\for SAS.xlsm"

3333 DBMS=EXCEL REPLACE;

3334 RANGE="ref$";

3335 GETNAMES=YES;

3336 MIXED=NO;

3337 SCANTEXT=YES;

3338 USEDATE=YES;

3339 SCANTIME=YES;

3340 RUN;

NOTE: WORK.REF1 data set was successfully created.

NOTE: The data set WORK.REF1 has 54 observations and 6 variables.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.26 seconds

cpu time 0.17 seconds

3341 data test;

3342 set test1 (keep=T Sample Cn V S A);

3343 group=scan(Sample,1);

3344 sqrtTime=sqrt(T);

3345 CnV=Cn*V;

3346 run;

NOTE: Missing values were generated as a result of performing an operation on missing values.

Each place is given by: (Number of times) at (Line)Column).

23 at 3344:12 23 at 3345:9

NOTE: There were 53 observations read from the data set WORK.TEST1.

NOTE: The data set WORK.TEST has 53 observations and 9 variables.

NOTE: DATA statement used (Total process time):

real time 0.03 seconds

cpu time 0.01 seconds

3347

3348 * Deriving the dependent variable Q for the regression in each Sample;

3349 data test;

3350 set test;

3351 by group;

3352 lagCn=lag(Cn);

3353 retain G1;

3354 if first.group then do;

3355 G1=S;

3356 sumCiS=0;

3357 Q=(CnV+SumCiS)/A;

3358 end;

3359 else do;

3360 SumCiS+(lagCn*G1);

3361 Q=(CnV+SumCiS)/A;

3362 end;

3363 run;

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

T=5 Sample=TLGT-6-5 Cn=8.04 V=7 S=0.5 A=1.76625 group=TLGT sqrtTime=2.2360679775 CnV=56.28

FIRST.group=0 LAST.group=0 lagCn=. G1=0.5 sumCiS=82.28 Q=. _ERROR_=1 _N_=30

NOTE: The SAS System stopped processing this step because of errors.

NOTE: There were 31 observations read from the data set WORK.TEST.

WARNING: The data set WORK.TEST may be incomplete. When this step was stopped there were 29

observations and 13 variables.

WARNING: Data set WORK.TEST was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

3364 data ref;

3365 set ref1 (keep=T Sample Cn V S A);

3366 group=scan(Sample,1);

3367 sqrtTime=sqrt(T);

3368 CnV=Cn*V;

3369 run;

NOTE: Missing values were generated as a result of performing an operation on missing values.

Each place is given by: (Number of times) at (Line)Column).

24 at 3367:12 24 at 3368:9

NOTE: There were 54 observations read from the data set WORK.REF1.

NOTE: The data set WORK.REF has 54 observations and 9 variables.

NOTE: DATA statement used (Total process time):

real time 0.03 seconds

cpu time 0.01 seconds

3370 * Deriving the dependent variable Q for the regression in each Sample;

3371 data ref;

3372 set ref;

3373 by group;

3374 lagCn=lag(Cn);

3375 retain G1;

3376 if first.group then do;

3377 G1=S;

3378 sumCiS=0;

3379 Q=(CnV+SumCiS)/A;

3380 end;

3381 else do;

3382 SumCiS+(lagCn*G1);

3383 Q=(CnV+SumCiS)/A;

3384 end;

3385 run;

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

T=5 Sample=RLD-6-5 Cn=7.35 V=7 S=0.5 A=1.76625 group=RLD sqrtTime=2.2360679775 CnV=51.45

FIRST.group=0 LAST.group=0 lagCn=. G1=0.5 sumCiS=66.845 Q=. _ERROR_=1 _N_=30

NOTE: The SAS System stopped processing this step because of errors.

NOTE: There were 31 observations read from the data set WORK.REF.

WARNING: The data set WORK.REF may be incomplete. When this step was stopped there were 29

observations and 13 variables.

WARNING: Data set WORK.REF was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

real time 0.03 seconds

cpu time 0.03 seconds

3386 data work.combine;

3387 set work.test work.ref;

3388 by Sample;

3389 run;

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

T=5 Sample=RLD-6-5 Cn=7.35 V=7 S=0.5 A=1.76625 group=RLD sqrtTime=2.2360679775 CnV=51.45

FIRST.Sample=1 LAST.Sample=1 _ERROR_=1 _N_=30

NOTE: The SAS System stopped processing this step because of errors.

NOTE: There were 1 observations read from the data set WORK.TEST.

NOTE: There were 31 observations read from the data set WORK.REF.

WARNING: The data set WORK.COMBINE may be incomplete. When this step was stopped there were 29

observations and 9 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.00 seconds

3390 proc print data=work.Combine;

3391 title 'Data Set TLGT Vs RLD';

3392 run;

NOTE: There were 29 observations read from the data set WORK.COMBINE.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

3393 proc sort data=work.test; by sqrtTime;

3394 run;

NOTE: There were 53 observations read from the data set WORK.TEST.

NOTE: The data set WORK.TEST has 53 observations and 9 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

3395 proc means data=work.test noprint;

3396 by sqrtTime;

3397 var Q;

ERROR: Variable Q not found.

3398 output out=summaryT mean=AverageQT;

3399 run;

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.SUMMARYT may be incomplete. When this step was stopped there were 0

observations and 0 variables.

NOTE: PROCEDURE MEANS used (Total process time):

real time 0.01 seconds

cpu time 0.00 seconds

3400 proc sort data=work.ref; by sqrtTime;

3401 run;

NOTE: There were 54 observations read from the data set WORK.REF.

NOTE: The data set WORK.REF has 54 observations and 9 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.01 seconds

cpu time 0.00 seconds

3402 proc means data=work.ref noprint;

3403 by sqrtTime;

3404 var Q;

ERROR: Variable Q not found.

3405 output out=summaryR mean=AverageQR;

3406 run;

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.SUMMARYR may be incomplete. When this step was stopped there were 0

observations and 0 variables.

NOTE: PROCEDURE MEANS used (Total process time):

real time 0.01 seconds

cpu time 0.00 seconds

3407

3408 data twosets;

3409 set summaryT;

3410 set SummaryR;

3411 run;

NOTE: There were 0 observations read from the data set WORK.SUMMARYT.

NOTE: The data set WORK.TWOSETS has 0 observations and 0 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

3412

3413 proc print data=twosets;

3414 title 'Average Cumulative release of TLGT and RLD';

3415 run;

NOTE: No variables in data set WORK.TWOSETS.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

3415! ;

3416

3417 proc sgplot data=work.combine1

3418 (where=(sqrtTime >= 0 and _freq_ = 6));

ERROR: File WORK.COMBINE1.DATA does not exist.

3419 title "TLGT Vs RLD";

3420 series x=SqrtTime y=AverageQR;

ERROR: No data set open to look up variables.

ERROR: No data set open to look up variables.

3421 series x=SqrtTime y=AverageQT;

ERROR: No data set open to look up variables.

ERROR: No data set open to look up variables.

3422 run;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SGPLOT used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

3423 title;

Accepted Solutions

Solution

01-27-2017
10:40 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-27-2017 04:28 PM

From long experience: the fact that you fixed your problem __for now__ shall not keep you from redesigning your process for greater stability, in light of the issues already mentioned.

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

Maxims of Maximally Efficient SAS Programmers

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-27-2017 03:27 PM

Your first ERROR is related to the DATA step with the BY statement in it. You data must first be sorted by the key variables in that BY statement. That means you need a PROC SORT step ahead of that.

If this is a program that you inherited and don't know how to maintain, you'll probably need to work with the author or someone else versed with the program. This forum can help you to solve specific questions, but you might need a little more guidance and orientation to SAS programming, There are lots of free resources if you're willing to learn.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-27-2017 04:05 PM

thank you for your reply. the person has left who worked on this program.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-27-2017 04:10 PM

Moments like these are ideal for a redesign and rewrite. The fact that a person who tolerated xlsm as SAS input has left your company is a good beginning.

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

Maxims of Maximally Efficient SAS Programmers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-27-2017 03:29 PM

The first error is:

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

So sort the dataset by the variables listed in your BY statement before the DATA step.

proc sort data=test; by group; run;

You have a similar error for your REF dataset and the fix is similar.

From reviewing the rest of your code:

Note that using this type of coding may lead to errors that are harder to debug:

data test;

set test;

...

Run;

I think the COMBINE data step may have a logical error, it's not common to see a SET statement with a BY statement.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-27-2017 03:37 PM

It's probably because you got sorted data in earlier times, and suddenly it is not so.

Absolutely no surprise, since your "data" comes from - tadaa - Excel, which is arguably the worst source one can think of.

So I strongly suggest you change the file transfer format to something text-based, and read the data with a customized data step, which guarantees consistent data (or an error message if the data does not adhere to the agreed-upon format).

And add the necssary proc sort so that the order is forced before the "by" step runs.

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

Maxims of Maximally Efficient SAS Programmers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-27-2017 04:07 PM

Thank you for your reply.

i was able to remove some of the sort errors by changing the way it imports (RANGE="Test$_xlnm#Print_Area" from excel. i have few errors left as follows:

4269 proc sgplot data=work.combine1

4270 (where=(sqrtTime >= 0 and _freq_ = 6));

ERROR: File WORK.COMBINE1.DATA does not exist.

4271 title "TLGT Vs RLD";

4272 series x=SqrtTime y=AverageQR;

ERROR: No data set open to look up variables.

ERROR: No data set open to look up variables.

4273 series x=SqrtTime y=AverageQT;

ERROR: No data set open to look up variables.

ERROR: No data set open to look up variables.

4274 run;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SGPLOT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

4275 title;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-27-2017 04:14 PM

That means that COMBINE1 was not created in a previous step. Go "up" in the log until you find the code that should create that dataset.

Maxims of Maximally Efficient SAS Programmers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-27-2017 04:18 PM

I was able to fix those errors. Thank you all for your help. I am so happy to see such helpful community. Thank you once again.

Solution

01-27-2017
10:40 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-27-2017 04:28 PM

From long experience: the fact that you fixed your problem __for now__ shall not keep you from redesigning your process for greater stability, in light of the issues already mentioned.

Maxims of Maximally Efficient SAS Programmers