topic Re: Proc import issue where the data is both comma separated & also the value have a tab or ente in SAS Programming

Proc import issue where the data is both comma separated & also the value have a tab or enter space

sas_student1 — Thu, 15 Sep 2022 17:51:30 GMT

I have a CSV file that is being extracted using API (see table 1 below). I am importing that data into SAS so I can clean it up. As I am using proc I realize that some cells have two values that looks like are separated by a tab (see column flat_value first=John last=Doe). So when I import the data wherever the values are separated by a tab they show up in a new line and under the incorrect column (see table 2).

Table1:

PID	field	label	flat_value	client_id
1	121697600	Hosp Type	Clubhouse	A
2	121697753	Reporting Period	6/1/2022	A
3	121698840	Confirm Hosp Type	Oneonta Teen Center	A
4	121718909	What is the name?	first = John last = Doe	A
5	121718930	Email Address	John.doe@aol.com	A
6	127510515	Does you have bilingual staff?	No	A
7	121838918	Total Visits	0	A
8	121838577	Unique Individuals Served	0	A

Table 2:

PID	field	label	flat_value	client_id
1	121697600	Hosp Type	Clubhouse	A
2	121697753	Reporting Period	6/1/2022	A
3	121698840	Confirm Hosp Type	Oneonta Teen Center	A
4	121718909	What is the name?	"first = John
last = Doe"	A
5	121718930	Email Address	John.doe@aol.com	A
6	127510515	Does you have bilingual staff?	No	A
7	121838918	Total Visits	0	A
8	121838577	Unique Individuals Served	0	A

This is the code being used.

PROC IMPORT 
OUT= want
DATAFILE= "C:\have.csv" 
            DBMS=csv;
     GETNAMES=YES;
RUN;

Suggestions on how to deal with the possible tab or space in one cell can be handled. I would want it to either (1) stay in the same cell but may have have a separator, or (2) have its own column or row if possible.

Thanks!

Re: Proc import issue where the data is both comma separated & also the value have a tab or ente

Tom — Thu, 15 Sep 2022 18:35:18 GMT

Not a TAB. A TAB would not cause that problem. Instead it looks like someone inserted end of line characters into the cell.

If you are extra lucky the character is a single LF or CR and the actual lines end with a CR and LF. You can then use the TERMSTR=CRLF option on the INFILE statement (or if you want to try letting SAS GUESS how to read it using PROC IMPORT on the FILENAME statement).

filename csv 'c:\have.csv' termstr=crlf;
PROC IMPORT 
  OUT= want replace
  DATAFILE= csv
  DBMS=csv
;
RUN;

If you are only a little bit lucky the values with the end of line characters in them a quoted. Then you can use something like this %replace_crlf() macro to first pre-process the file and replace those embedded end of line characters with something else so SAS can parse the file into the right number of records.

And if you are not lucky at all then open the file with an actual text editor and remove the line breaks by hand.

Re: Proc import issue where the data is both comma separated & also the value have a tab or ente

Ksharp — Fri, 16 Sep 2022 12:40:18 GMT

@Tom and me talk a lot about this question at this :

https://communities.sas.com/t5/SAS-Programming/Input-a-CSV-file-with-text-fields-that-contain-line-breaks/m-p/833326#M329416