Remove tab separation

[GS2]

Using SAS 9.4

I am submitting data and the format is specific that each field is to be separated by space only and not by tabs. Is there a good method in SAS to use to accomplish this? Thank you

[Reeza]

Unless you specify tab as the delimiter, SAS will default to whatever you set as the delimiter, so a space should be fine.

How are you exporting your data that causes this to be an issue?

Otherwise use PROC EXPORT with DLM= " " or use a manual data step.

If you have text data that may have tabs that you need to remove you can use COMPRESS() for that.

[GS2]

I imported using proc import with a CSV file. I exported the file as a text file (also specified in the format) and was told there are tabs separating each field and that it needs to be a space separating them. Would the compress function do that? 

[ballardw]

It might help to show the code you actually used to export the data.

[GS2]

proc import datafile='location'
dbms= csv
out= NDI.DATA_LABELS (KEEP= PATIENT_NAME DOB GENDER DATE_OF_DEATH LAST_CLINIC_FOLLOW_UP)
replace;
GUESSINGROWS= 333;
GETNAMES = YES;
run;

[Tom]

That is code to READ from a delimited file, not create a delimited file.

Note if you used that code to read from a file that used TAB as the delimiter instead of the COMMA you told PROC IMPORT to look for then PROC IMPORT will make the wrong guesses about what is in the file.  And any tabs will show up in the value of the variables created by PROC IMPORT.

[GS2]

So I need to re-import my data in a way that does not include tabs in the data?

[Tom]

Take two steps back and start over.

Do you have the data or not?  What format is it in?  Is it in a SAS dataset? Or perhaps in database that you linked to using a libref so that you can tread it as a SAS dataset.   If you don't have the data in a dataset then you will need to re-run whatever code you used before to make the dataset.

I have no idea what button you clicked on to export, or even what user interface you are using to make a guess at what button you pushed.  But it is trivial to write a PROGRAM to create a text file from a dataset.  Just use a DATA step that reads in the existing dataset and writes the file.  For example here is a program to write SASHELP.CLASS dataset to a text file.

data _null_;
  set sashelp.class ;
  file 'myfile.txt' ;
  put @1 name
        @10 sex
        @15 age
        @20 height
        @30 weight
  ;
run;

[GS2]

The data is a csv file that I have imported into SAS and then formatted to meet the specifications with the exception that I need the text file to not have tabs separating the data. 

When you right-click on the sas file name and click export, that is how I am exporting my data. It exports to .txt but it is tab delimited, how can I do this where it is not tab delimited?

[Tom]

You still didn't say what interface you are using (SAS/Studio, Enterprise Guide, SAS Display Manager, etc, etc) or what menu items you are selecting and choices you a making when exporting the file.  But in general I don't think many of those user interfaces offer a point and click way to generate a fixed column text file.

So just write the program. Sounds like you already know what variables you have since you created them from the data read from the CSV file.

[Reeza]

Right click and export is the incorrect method for creating a data set in a specified format. 

You can try PROC EXPORT but if you have character variables that have embedded spaces it can be quite problematic. 

Usually you need either a fixed width file or a space delimited file. They are not the same thing and you need to be sure of what you want. 

In a fixed width file, all of a specific variable all start at the same column regardless of length. In comparison a space delimited has a single (or double space) between each set of variables. 

This is one method to create a space delimited file. If you need a fixed width file, you must write the code manually via a data step, as @Tom has already illustrated for you. 

proc export data=sashelp.class outfile='/home/fkhurshed/Demo1/text.txt' dbms=dlm; delimiter=" ";
run;

---

@GS2 wrote:

The data is a csv file that I have imported into SAS and then formatted to meet the specifications with the exception that I need the text file to not have tabs separating the data. 

When you right-click on the sas file name and click export, that is how I am exporting my data. It exports to .txt but it is tab delimited, how can I do this where it is not tab delimited?

---

[Tom]

---

@GS2 wrote:

I imported using proc import with a CSV file. I exported the file as a text file (also specified in the format) and was told there are tabs separating each field and that it needs to be a space separating them. Would the compress function do that? 

---

What exactly did you do to achieve "exported the file as a text file"?

Do you have a document that describes the format they want? 

It is not normal to create a space delimited file since it is very likely that text values will contain spaces.  It is more common to create a fixed format file where each field takes a fixed number of (single byte) characters.  In that type of file the "empty" space will be filled with the blank character.  So if the first field is allocated 10 characters and the value only needs 5 characters to represent it then 5 spaces are written to the file before the second field so that the second field always starts in the 11th column.  Note if you are using a character encoding (such as UTF-8) where some characters require more than one byte it is difficult to create a fixed format file. So either limit your data to 7-bit ASCII characters or use a different format.

The only way tabs will get into the output file is if you put them there. Either by using them as delimiter then exporting or because the values of the variables themselves have tabs.  If the later is true what do they want you to do?  Should the tabs stay in the value? Be replaced by some other character, or characters?  Stripped from the values?

[GS2]

I click on the file in my library and export as a text file. 

It is for a NDI data submission and they have specific guidelines.

You are correct that each position has its own set length so column 1 always needs to be 20 character spaces etc. The feedback I received is that the data had tabs in it and they needed to be removed for spaces only. Is this something the compress function can accomplish?

[Tom · Accepted solution]

Do you have a link to the format?  Is it this one? https://www.cdc.gov/nchs/data/ndi/ndi_users_guide_chapter2.pdf

So just use something like this:

data _null_;
  set have ;
  file 'transmittal.txt' lrecl=100 pad;
  put 
    LAST_NAME $20.
    FIRST_NAME $15.
    MIDDLE_INITIAL $1.
    SOCIAL_SECURITY_NUMBER $9.
    MONTH_OF_BIRTH $3.
    DAY_OF_BIRTH $2.
    YEAR_OF_BIRTH $4.
    FATHERS_SURNAME $18.
    AGE_UNIT $1.
    NUMBER_OF_AGE_UNITS $2.
    SEX $1.
    RACE $1.
    MARITAL_STATUS $1.
    STATE_OF_RESIDENCE $2.
    STATE_OF_BIRTH $2.
    ID_NUMBER $10.
    USER_DATA $6.
  ;
run;

Or use the @ method in one of my previous posts. Note that will be easier if you only have some of those variables.

But make sure when using that method the values being written are not longer than the space allocated for them.  For example if the value of LAST_NAME was 28 characters long and FIRST_NAME as only 4 characters long and you did this simple syntax:

put @1 last_name @21 first_name ..... ;

The 21st to 24th character of the over-long last_name would get replaced by the first_name, but the other extra characters would still be there on the line and they will be read as part of the first_name value.

[GS2]

Yes that is the correct format and I have the data formatted like that. However, how do I export the data show that it is a text file that is not delimited in any way?