how to create standard text file format for National death index in sas or R

Question

I want to create a standard text file format with 100 positions per patient as in the attached screenshot ( ignoring the first 2 rows) to get the death date as requested by the National Death Index. I tried that in sas and then exported it as .txt but I noticed that columns width were not properly aligned upon opening that in the Notepad. They don't want to keep headings but we need to keep stated width.

Here is my sample fictitious dataset (that I have in Excel) and my used code

Abei    Ghador          07  16  1992                                            
Aziz    Patrick         07  01  1947                                            
Ali Theodore            07  01  1966                                            
Abdelrahman Baker   A       05  01  1966                                            
Abd Farik           11  01  1971                                            
Abele   Mat A       04  01  1994

Here are some of my efforts in sas as I kept merging each column to the combined data but the final .txt did not meet their requirements.

data combinedAoDB;  merge LastName1 FirstName2 ;run;
data combinedAoDB;  merge combinedAoDB MIDDLEINITIAL3 SSN4 MONTHBIRTH5 DAYBIRTH6 YEARBIRTH5;run;
data combinedAoDB;  merge combinedAoDB FATHERSURNAME8 AGEUNITdeath9 NUMBERAGEUNITSdth_10 SEX_11 RACE_12 MARITALSTATUS_13 STATEOFRESIDENCE_14;run;
data combinedAoDB;  merge combinedAoDB STATEOFBIRTH_15 CONTROLIDNUMBER_16 OPTIONALUSERDATA_17 BLANKFIELD_18;run;


proc contents data=combinedAoDB;run;

proc export data=work.combinedAoDB outfile='new 3 combinedAoDB.txt' 
replace; putnames=no; run;

Any advice on how to fix that in sas or R will be greatly appreciated.

Answer 1

You can use a columnar PUT statement. Output values longer than the specified column range will be truncated. Output values are according to the variable's format.

Example:

Presume your data has column names as shown.

put
  LAST_NAME       1-20
  FIRST_NAME     21-35
  MIDDLE INITIAL 36
  SSN            37-45
  DOB_MONTH      46-47
  DOB_DAY        48-49
  DOB_YEAR       50-53
  FATHER_SURNAME 54-71
  AGE_UNIT       72
  AGE            73-74
  SEX            75
  RACE           76
  MARITAL_STATUS 77
  STATE_RESIDE   78-79
  STATE_BIRTH    80-81
  CONTROL_ID     82-91
  USER_DATA      92-97
  BLANK          98-100
;

Answer 2

You cannot "export" to a fixed length file. And you don't need to. You just WRITE the fixed length file directly.

 data _null_;
    set work.combinedAoDB;
    file 'new 3 combinedAoDB.txt' lrecl=100 pad;
    put @1  lastname $20.
        @21 firstname $15.
        @36 middleinitial $1.
        ...
        @46 month Z2. day Z2. year Z4.
        ...
    ;
run;

If you have the text in that first picture as actual text (or already as a dataset) then you can use it to generate the PUT statement. But for just 17 variables you can just type it out yourself.

Let's try some of your example data:

data have;
  infile cards dsd dlm='|' truncover;
  input lastname :$20. firstname :$25. middleinitial :$1. month day year;
cards4;
Abei|Ghador||07|16|1992                                            
Aziz|Patrick||07|01|1947                                            
Abele|Mat|A|04|01|1994
;;;;

filename fda temp;

data _null_;
  set have;
  file fda lrecl=100 pad;
  put @1  lastname $20.
      @21 firstname $15.
      @36 middleinitial $1.
      @46 month Z2. day Z2. year Z4.
  ;
run;

data _null_;
  infile fda ;
  input;
  list;
run;

Result

RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
1         Abei                Ghador                   07161992                                                100
2         Aziz                Patrick                  07011947                                                100
3         Abele               Mat            A         04011994                                                100