topic Re: how to export sas dataset into text file using datastep when we have missing records in SAS Programming

how to export sas dataset into text file using datastep when we have missing records

Shanthi123 — Wed, 03 Apr 2024 06:41:37 GMT

Hello ,

Can someone help to get the proper text file ?

i want to export sas dataset into text file using datastep.

below is the code which i was using to export for 80 variables , i just added one more variable load_no in this i have missing records. because of that my output file is not giving in proper format.

DATA ORD_TO_BIN;
    SET WORK.ORD_TO_BIN;
    FILE "/sasdata/trnsp_freight_analytics/production_jobs/scv/SCV_RAW_FILE_MR_SNOW&sysdate9..txt";
PUT
@1 REPORT_ID         $CHAR1.
         @2 REPORT_DT         YYMMDD10.
         @12 CUST_REF         $CHAR25.
         @37 ORD_CLS_CD       $CHAR1.
         @38 SRC_CUST_CD      $CHAR6.
         @44 FROM_NAME        $CHAR25.
         @69 CUST_CD          $CHAR6.
         @75 TO_NAME          $CHAR25.
         @100 SRC_CUST_TYP    $CHAR3.
         @103 CP_DIR_SHP_IND  $CHAR1.
         @104 STD_DPLYTYP_LTMDAY 3.
         @107 CNTR_NO         $CHAR15.
         @122 STR_MATL_RQST_ID $CHAR10.
         @132 PRIM_PSO        $CHAR9.
         @141 ID_PART         $CHAR20.
         @161 PART_TYP_ID     $CHAR2.
         @163 MATL_RQST_QTY   7.
         @170 ORD_ENT_DT      YYMMDD10.
         @180 ORD_ENT_TM      TIME8.
         @188 XD_ACT_DEP_DT   YYMMDD10.
         @198 XD_ACT_DEP_TM   TIME8.
         @206 XD_ACT_ARVL_DT  YYMMDD10.
         @216 XD_ACT_ARVL_TM  TIME8.
         @224 ACT_DEP_DT      YYMMDD10.
         @234 ACT_DEP_TM      TIME8.
         @242 ACT_ARVL_DT     YYMMDD10.
         @252 ACT_ARVL_TM     TIME8.
         @260 RCPT_ACPT_DT    YYMMDD10.
         @270 RCPT_ACPT_TM    TIME8.
         @278 CMPLT_DT        YYMMDD10.
         @288 CMPLT_TM        TIME8.
         @296 BIN_CAL_DAYS    6.2
         @302 BIN_WRK_HRS     6.2
         @308 BIN_OPN_DAYS    6.2
         @314 XD_ORD_TO_SHP   6.2
         @320 SHP_STD         6.2
         @326 SHP_DIFF        6.2
         @332 XD_TRN_TO_XDK   6.2
         @338 TXD_STD         6.2
         @344 TXD_DIFF        6.2
         @350 XD_ARV_TO_SHP   6.2
         @356 XDK_STD         6.2
         @362 XDK_DIFF        6.2
         @368 XD_SHP_TO_ARV   6.2
         @374 TRN_STD         6.2
         @380 TRN_DIFF        6.2
         @386 XD_ARV_TO_BIN   6.2
         @392 REC_STD         6.2
         @398 REC_DIFF        6.2
         @404 XD_ORD_TO_BIN   6.2
         @410 BIN_STD         6.2
         @416 BIN_DIFF        6.2
         @422 STR_LOC_ID      $CHAR10.
         @432 PICK_LOC_ID     $CHAR10.
         @442 SHP_MATL_RQST_ID $CHAR10.
         @452 CASE_NO         $CHAR8.
         @460 SYS_SHP_ID      $CHAR10.
         @470 XD_CRSDOCK_IND  $CHAR1.
         @471 XD_SYS_SHP_ID   $CHAR10.
         @481 XD_SRC_CUST_CD  $CHAR6.
         @487 XD_SYS_CNTR_ID  $CHAR10.
         @497 XD_CNTR_NO      $CHAR15.
          @512  LOAD_NO               $CHAR9.
;
RUN;

this is how am expecting the output to be readable but the i am getting output as the values are replacing the missing places with the next record.. can someone help.

Re: how to export sas dataset into text file using datastep when we have missing records

Patrick — Wed, 03 Apr 2024 07:52:03 GMT

Not sure why this should be happening if these repeated values aren't already in your source data. You could try and add lrecl=530 to your file statement.

data _null_;
  file "%sysfunc(pathname(work))\test.txt" lrecl=530;
  set sashelp.class;
  if _n_ ne 3 then name2=name;
  put @1 name $char8. @512 name2 $char8.;
run;

Re: how to export sas dataset into text file using datastep when we have missing records

ballardw — Wed, 03 Apr 2024 13:41:14 GMT

You need to provide example data.

Your comment " this is how am expecting the output to be readable but the i am getting output as the values are replacing the missing places with the next record". sounds a lot like you have attempted to read the resulting file back into SAS. If that is the case, did you read the LOG and fine a note like this?

NOTE: SAS went to a new line when INPUT statement reached past the end of a line.

That would indicate you did not have one of the options such as MISSOVER or TRUNCOVER on the INFILE statement to tell SAS how to handle incomplete lines of data.

Re: how to export sas dataset into text file using datastep when we have missing records

Tom — Wed, 03 Apr 2024 14:01:43 GMT

I do not understand what you mean. Examples will really help clarify the issue.

I do not understand what you meaning about missing records. Do you mean the file has too few lines for the number of observations in the original dataset? That should be impossible with the PUT statement you showed.

Do you mean that the original dataset is missing some observations? That is an independent problem from the writing a text file.

Perhaps you mean the values from some variables are bleeding into the columns in the text file used by another variable. That can happen with that style of PUT statement using the @ cursor motion and formatted variables if you miscalculate the @ locations and the format widths.

Re: how to export sas dataset into text file using datastep when we have missing records

Shanthi123 — Thu, 25 Apr 2024 12:19:39 GMT

@Tom i was not able to attach the screenshot or data file to show. i have used CSV file to export instead of text file. am not sure why am not able to export into text. i think its because of data issue.

thanks

Re: how to export sas dataset into text file using datastep when we have missing records

ballardw — Thu, 25 Apr 2024 13:51:49 GMT

@Shanthi123 wrote:

@Tom i was not able to attach the screenshot or data file to show. i have used CSV file to export instead of text file. am not sure why am not able to export into text. i think its because of data issue.

thanks

CSV is text, originally Comma Separated Values though today some seem to use it for Character Separated.

The difference is the presence of the viruses that think that CSV should only be opened in spreadsheet software.

You can force a CSV to open in a text file viewer such as Notepad or even the SAS editor by using a file menu in that application or right click and what ever version of "open with" another program is available.

Note: if you open a CSV file in spreadsheet software and then save it then values may well have changed.