PROC EXPORT bug? Placement of WHERE and KEEP in dataset options

[JacquesR]

Hi all

I have just experienced a weird situation that I cannot find documentation on.

The focus is on the log, which is really all you should need, but because I know _someone_ is going to complain about me not posting code, consider these two SAS statements:

PROC EXPORT DATA=CHE.cohort
                  (WHERE=(InAPDC) KEEP=PPN NumRecsPerCaseAPDC InAPDC NumRecsPerCaseCOD InCOD NumRecsPerCaseRBDM InRBDM APDC_TXY_case N_APDC_TXY_events COD_TXY_case PoisoningSubcohort Sex BirthDate death_dateCOD death_dateRBDM)
      OUTFILE="l:\Cohort.csv"
      DBMS=CSV
      REPLACE;
RUN;

PROC EXPORT DATA=CHE.cohort
                  (KEEP=PPN NumRecsPerCaseAPDC InAPDC NumRecsPerCaseCOD InCOD NumRecsPerCaseRBDM InRBDM APDC_TXY_case N_APDC_TXY_events COD_TXY_case PoisoningSubcohort Sex BirthDate death_dateCOD death_dateRBDM
  WHERE=(InAPDC))
      OUTFILE="l:\Cohort.csv"
      DBMS=CSV
      REPLACE;
RUN;

The only difference is that in the first, the WHERE statement is before the KEEP and after in the second.

Now look at the two logs below.

For the first PROC EXPORT statement, the first variable (ppn) is not exported. I can't understand why the placement of the WHERE statement should cause one variable to be dropped?

Has anyone seen this, or can explain it?

1271 PROC EXPORT DATA=CHE.COHORT
1272 (WHERE=(InAPDC) KEEP=PPN NumRecsPerCaseAPDC InAPDC NumRecsPerCaseCOD InCOD
1272! NumRecsPerCaseRBDM InRBDM APDC_TXY_case N_APDC_TXY_events COD_TXY_case PoisoningSubcohort Sex
1272! BirthDate death_dateCOD death_dateRBDM)
1273 OUTFILE="l:\Cohort.csv"
1274 DBMS=CSV
1275 REPLACE;
1276 RUN;

1277 /**********************************************************************
1278 * PRODUCT: SAS
1279 * VERSION: 9.4
1280 * CREATOR: External File Interface
1281 * DATE: 27MAY26
1282 * DESC: Generated SAS Datastep Code
1283 * TEMPLATE SOURCE: (None Specified.)
1284 ***********************************************************************/
1285 data _null_;
1286 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
1287 %let _EFIREC_ = 0; /* clear export record count macro variable */
1288 file 'l:\Cohort.csv' delimiter=',' DSD DROPOVER
1288! lrecl=32767;
1289 if _n_ = 1 then /* write column names or labels */
1290 do;
1291 put
1292 ','
1293 "NumRecsPerCaseAPDC"
1294 ','
1295 "InAPDC"
1296 ','
1297 "NumRecsPerCaseCOD"
1298 ','
1299 "InCOD"
1300 ','
1301 "NumRecsPerCaseRBDM"
1302 ','
1303 "InRBDM"
1304 ','
1305 "APDC_TXY_case"
1306 ','
1307 "N_APDC_TXY_events"
1308 ','
1309 "COD_TXY_case"
1310 ','
1311 "PoisoningSubcohort"
1312 ','
1313 "Sex"
1314 ','
1315 "BirthDate"
1316 ','
1317 "death_dateCOD"
1318 ','
1319 "death_dateRBDM"
1320 ;
1321 end;
1322 set CHE.COHORT(WHERE=(InAPDC) KEEP=PPN NumRecsPerCaseAPDC InAPDC
1322! NumRecsPerCaseCOD InCOD NumRecsPerCaseRBDM InRBDM APDC_TXY_case N_APDC_TXY_events
1322! COD_TXY_case PoisoningSubcohort Sex
1323 BirthDate death_dateCOD death_dateRBDM) end=EFIEOD;
1324 format NumRecsPerCaseAPDC best12. ;
1325 format InAPDC best12. ;
1326 format NumRecsPerCaseCOD best12. ;
1327 format InCOD best12. ;
1328 format NumRecsPerCaseRBDM best12. ;
1329 format InRBDM best12. ;
1330 format APDC_TXY_case best12. ;
1331 format N_APDC_TXY_events best12. ;
1332 format COD_TXY_case best12. ;
1333 format PoisoningSubcohort best12. ;
1334 format Sex bestx12. ;
1335 format BirthDate date9. ;
1336 format death_dateCOD date9. ;
1337 format death_dateRBDM date9. ;
1338 do;
1339 EFIOUT + 1;
1340 put NumRecsPerCaseAPDC @;
1341 put InAPDC @;
1342 put NumRecsPerCaseCOD @;
1343 put InCOD @;
1344 put NumRecsPerCaseRBDM @;
1345 put InRBDM @;
1346 put APDC_TXY_case @;
1347 put N_APDC_TXY_events @;
1348 put COD_TXY_case @;
1349 put PoisoningSubcohort @;
1350 put Sex @;
1351 put BirthDate @;
1352 put death_dateCOD @;
1353 put death_dateRBDM ;
1354 ;
1355 end;
1356 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
1357 if EFIEOD then call symputx('_EFIREC_',EFIOUT);
1358 run;

1372 PROC EXPORT DATA=CHE.cohort
1373 (KEEP=PPN NumRecsPerCaseAPDC InAPDC NumRecsPerCaseCOD InCOD
1373! NumRecsPerCaseRBDM InRBDM APDC_TXY_case N_APDC_TXY_events COD_TXY_case PoisoningSubcohort Sex
1373! BirthDate death_dateCOD death_dateRBDM
1374 WHERE=(InAPDC))
1375 OUTFILE="l:\Cohort.csv"
1376 DBMS=CSV
1377 REPLACE;
1378 RUN;

1379 /**********************************************************************
1380 * PRODUCT: SAS
1381 * VERSION: 9.4
1382 * CREATOR: External File Interface
1383 * DATE: 27MAY26
1384 * DESC: Generated SAS Datastep Code
1385 * TEMPLATE SOURCE: (None Specified.)
1386 ***********************************************************************/
1387 data _null_;
1388 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
1389 %let _EFIREC_ = 0; /* clear export record count macro variable */
1390 file 'l:\Cohort.csv' delimiter=',' DSD DROPOVER
1390! lrecl=32767;
1391 if _n_ = 1 then /* write column names or labels */
1392 do;
1393 put
1394 "PPN"
1395 ','
1396 "NumRecsPerCaseAPDC"
1397 ','
1398 "InAPDC"
1399 ','
1400 "NumRecsPerCaseCOD"
1401 ','
1402 "InCOD"
1403 ','
1404 "NumRecsPerCaseRBDM"
1405 ','
1406 "InRBDM"
1407 ','
1408 "APDC_TXY_case"
1409 ','
1410 "N_APDC_TXY_events"
1411 ','
1412 "COD_TXY_case"
1413 ','
1414 "PoisoningSubcohort"
1415 ','
1416 "Sex"
1417 ','
1418 "BirthDate"
1419 ','
1420 "death_dateCOD"
1421 ','
1422 "death_dateRBDM"
1423 ;
1424 end;
1425 set CHE.COHORT(KEEP=PPN NumRecsPerCaseAPDC InAPDC NumRecsPerCaseCOD InCOD
1425! NumRecsPerCaseRBDM InRBDM APDC_TXY_case N_APDC_TXY_events COD_TXY_case PoisoningSubcohort Sex
1425! BirthDate
1426 death_dateCOD death_dateRBDM WHERE=(InAPDC)) end=EFIEOD;
1427 format PPN $14. ;
1428 format NumRecsPerCaseAPDC best12. ;
1429 format InAPDC best12. ;
1430 format NumRecsPerCaseCOD best12. ;
1431 format InCOD best12. ;
1432 format NumRecsPerCaseRBDM best12. ;
1433 format InRBDM best12. ;
1434 format APDC_TXY_case best12. ;
1435 format N_APDC_TXY_events best12. ;
1436 format COD_TXY_case best12. ;
1437 format PoisoningSubcohort best12. ;
1438 format Sex bestx12. ;
1439 format BirthDate date9. ;
1440 format death_dateCOD date9. ;
1441 format death_dateRBDM date9. ;
1442 do;
1443 EFIOUT + 1;
1444 put PPN $ @;
1445 put NumRecsPerCaseAPDC @;
1446 put InAPDC @;
1447 put NumRecsPerCaseCOD @;
1448 put InCOD @;
1449 put NumRecsPerCaseRBDM @;
1450 put InRBDM @;
1451 put APDC_TXY_case @;
1452 put N_APDC_TXY_events @;
1453 put COD_TXY_case @;
1454 put PoisoningSubcohort @;
1455 put Sex @;
1456 put BirthDate @;
1457 put death_dateCOD @;
1458 put death_dateRBDM ;
1459 ;
1460 end;
1461 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
1462 if EFIEOD then call symputx('_EFIREC_',EFIOUT);
1463 run;

[LinusH · Accepted solution]

This looks like some kind of a bug, report it to SAS tech support.

[JacquesR]

Ok. I did that. Thanks.

[Tom]

I cannot re-create your issue using SAS 9.4M8.

How was the CHE libref defined? Is it pointing to a directory with SAS datasets? Or something else, like a foreign database?  What happens if you change them both to using the same case for the dataset name (one is using CHE.COHORT and the other has CHE.cohort (lowercase)?  It should NOT matter, but perhaps if your libref CHE is not pointing at a library of SAS datasets then perhaps it does matter.

Also is there anything strange about the name of the variable that seems to go missing?

 73         %put &=sysvlong ;
 SYSVLONG=9.04.01M8P022223
 74         filename csv1 temp;
 75         filename csv2 temp;
 76         
 77         PROC EXPORT
 78           DATA=sashelp.class(where=(age=12) keep=name age sex)
 79           DBMS=CSV
 80           OUTFILE=csv1 replace
 81         ;
 82         RUN;
 
 NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to 
 WORK.PARMS.PARMS.SLIST.
 83          /**********************************************************************
 84          *   PRODUCT:   SAS
 85          *   VERSION:   9.4
 86          *   CREATOR:   External File Interface
 87          *   DATE:      01JUN26
 88          *   DESC:      Generated SAS Datastep Code
 89          *   TEMPLATE SOURCE:  (None Specified.)
 90          ***********************************************************************/
 91             data _null_;
 92             %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
 93             %let _EFIREC_ = 0;     /* clear export record count macro variable */
 94             file CSV1 delimiter=',' DSD DROPOVER ;
 95             if _n_ = 1 then        /* write column names or labels */
 96              do;
 97                put
 98                   "Name"
 99                ','
 100                  "Sex"
 101               ','
 102                  "Age"
 103               ;
 104             end;
 105           set  SASHELP.CLASS(where=(age=12) keep=name age sex)   end=EFIEOD;
 106               format Name $8. ;
 107               format Sex $1. ;
 108               format Age best12. ;
 109             do;
 110               EFIOUT + 1;
 111               put Name $ @;
 112               put Sex $ @;
 113               put Age ;
 114               ;
 115             end;
 116            if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
 117            if EFIEOD then call symputx('_EFIREC_',EFIOUT);
 118            run;
 
 NOTE: The file CSV1 is:
       Filename=/saswork/SAS_work7E2E0001F819_odaws02-usw2.oda.sas.com/#LN00192,
       Owner Name=tom.abernathy,Group Name=oda,
       Access Permission=-rw-r--r--,
       Last Modified=31May2026:21:51:56
 
 NOTE: 6 records were written to the file CSV1.
       The minimum record length was 9.
       The maximum record length was 12.
 NOTE: There were 5 observations read from the data set SASHELP.CLASS.
       WHERE age=12;
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 5 records created in CSV1 from SASHELP.CLASS.
   
   
 NOTE: "CSV1" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.02 seconds
       cpu time            0.02 seconds
       
 
 119        
 120        PROC EXPORT
 121          DATA=sashelp.class(keep=name age sex where=(age=12) )
 122          DBMS=CSV
 123          OUTFILE=csv2 replace
 124        ;
 125        RUN;
 
 NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to 
 WORK.PARMS.PARMS.SLIST.
 126         /**********************************************************************
 127         *   PRODUCT:   SAS
 128         *   VERSION:   9.4
 129         *   CREATOR:   External File Interface
 130         *   DATE:      01JUN26
 131         *   DESC:      Generated SAS Datastep Code
 132         *   TEMPLATE SOURCE:  (None Specified.)
 133         ***********************************************************************/
 134            data _null_;
 135            %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
 136            %let _EFIREC_ = 0;     /* clear export record count macro variable */
 137            file CSV2 delimiter=',' DSD DROPOVER ;
 138            if _n_ = 1 then        /* write column names or labels */
 139             do;
 140               put
 141                  "Name"
 142               ','
 143                  "Sex"
 144               ','
 145                  "Age"
 146               ;
 147             end;
 148           set  SASHELP.CLASS(keep=name age sex where=(age=12) )   end=EFIEOD;
 149               format Name $8. ;
 150               format Sex $1. ;
 151               format Age best12. ;
 152             do;
 153               EFIOUT + 1;
 154               put Name $ @;
 155               put Sex $ @;
 156               put Age ;
 157               ;
 158             end;
 159            if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
 160            if EFIEOD then call symputx('_EFIREC_',EFIOUT);
 161            run;
 
 NOTE: The file CSV2 is:
       Filename=/saswork/SAS_work7E2E0001F819_odaws02-usw2.oda.sas.com/#LN00193,
       Owner Name=tom.abernathy,Group Name=oda,
       Access Permission=-rw-r--r--,
       Last Modified=31May2026:21:51:56
 
 NOTE: 6 records were written to the file CSV2.
       The minimum record length was 9.
       The maximum record length was 12.
 NOTE: There were 5 observations read from the data set SASHELP.CLASS.
       WHERE age=12;
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 5 records created in CSV2 from SASHELP.CLASS.
   
   
 NOTE: "CSV2" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.02 seconds
       cpu time            0.03 seconds
       
 

 Also why did you not get the NOTE about not being able to write to SASUSER library? Are you accidentally running SAS without the RSASUSER option?

[JacquesR]

Hi @Tom 

I think we can put this to rest, as I indicated, I reported it to SAS support.

For the record, they were able to verify it in TS1M9, and it does not occur in TS1M8.

But leave it there and let them handle it.