topic Re: Error: BY variables are not properly sorted in SAS Procedures

Error: BY variables are not properly sorted

DShah — Fri, 27 Jan 2017 20:24:02 GMT

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;

Re: Error: BY variables are not properly sorted

ChrisHemedinger — Fri, 27 Jan 2017 20:27:09 GMT

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.

Re: Error: BY variables are not properly sorted

Reeza — Fri, 27 Jan 2017 20:29:35 GMT

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.

Re: Error: BY variables are not properly sorted

Kurt_Bremser — Fri, 27 Jan 2017 20:37:18 GMT

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.

Re: Error: BY variables are not properly sorted

DShah — Fri, 27 Jan 2017 21:05:37 GMT

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

Re: Error: BY variables are not properly sorted

DShah — Fri, 27 Jan 2017 21:07:50 GMT

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;

Re: Error: BY variables are not properly sorted

Kurt_Bremser — Fri, 27 Jan 2017 21:10:57 GMT

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.

Re: Error: BY variables are not properly sorted

Kurt_Bremser — Fri, 27 Jan 2017 21:14:12 GMT

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.

Re: Error: BY variables are not properly sorted

DShah — Fri, 27 Jan 2017 21:18:21 GMT

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.

Re: Error: BY variables are not properly sorted

Kurt_Bremser — Fri, 27 Jan 2017 21:28:38 GMT

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.