Import Pipe Delimited File into SAS Viya

[BruceTao]

Hi,

We are currently migrating our applications from SAS EG (6.1)  to SAS Viya. One application requires me to import a pipe delimited file using the following code. 

PROC IMPORT OUT = Export.TDSR_InterfaceTestOutDec19
       DATAFILE= '/sasshare/prd/AUDIT/BlackMESA/TDSR_InterfaceIn'
       DBMS=DLM REPLACE;
       DELIMITER = "|";
       GETNAMES = YES;
RUN;

However, this code imports all the fields as Character strings.

When I use the import function in SAS EG, importing the exact same file, it seems to recognized that characters are characters, numbers are numbers and data time values are data time values.

I want my imported SAS Viya file to be exactly the same as the SAS EG file. 

Thanks

[Reeza]

In general, with automated guessing procs, this is a hard task. Each system can make different guesses. If you require the same types each time, I would suggest not using PROC IMPORT and explicitly defining the types and lengths to ensure it's the format you need instead.

[BruceTao]

Hi, I am new to SAS so exactly how do you "explicitly define the types and
lengths" if you do not use PROC IMPORT?
Thanks for the quick response!!!

[Reeza]

When you run a PROC IMPORT, check the log. Copy that code and modify it as needed.

[Tom]

I am surprised that they would behave that differently for the same file.

Did you try for the exact same file or two versions that were supposed be similar data?

Can you share a few lines of the file? 

Use the Insert Code icon {i} to get a pop-up window that you can paste some lines from the file into.  That way this website won't try to reformat the lines.

[BruceTao]

1	Minimum interest rate (interface only) Changed	0023175086	E77319	20170712	11:25:11	U	ZZN40024	Minimum interest rate (interface only)	0.0000000	999.0000000-	1	0
2	Interest Settlement Frequency (interfac) Changed	0023175086	E77319	20170712	11:25:11	U	ZZN40029	Interest Settlement Frequency (interfac)		0	1	0
3	Amount Determ. Category (interface) Changed	0023175086	E77319	20170712	11:25:11	U	ZZN40030	Amount Determ. Category (interface)		0030	1	0
4	Total Monthly Payment Changed	0023175086	E77319	20170712	11:25:11	U	ZZN80014	Total Monthly Payment	0.00	405.09	1	0

[BruceTao]

Yes, I was using the exact same file. 

Bruce

[Tom]

There are no pipe characters in that data. So it only contains one variable.

[BruceTao]

"Action"|"ObjValue"|"User"|"Date"|"Time"|"ChangeID"|"FieldName"|"ShortText"|"OldValue"|"NewValue"|"COUNT"|"CC_STAFF_FLAG"
"Minimum interest rate (interface only) Changed"|"0023175086"|"E77319"|"20170712"|"11:25:11"|"U"|"ZZN40024"|"Minimum interest rate (interface only)"|"   0.0000000"|" 999.0000000-"|"1"|"0"
"Interest Settlement Frequency (interfac) Changed"|"0023175086"|"E77319"|"20170712"|"11:25:11"|"U"|"ZZN40029"|"Interest Settlement Frequency (interfac)"|""|"0"|"1"|"0"
"Amount Determ. Category (interface) Changed"|"0023175086"|"E77319"|"20170712"|"11:25:11"|"U"|"ZZN40030"|"Amount Determ. Category (interface)"|""|"0030"|"1"|"0"
"Total Monthly Payment Changed"|"0023175086"|"E77319"|"20170712"|"11:25:11"|"U"|"ZZN80014"|"Total Monthly Payment"|"                    0.00"|"                  405.09"|"1"|"0"

I hope this helps.

Thanks

Bruce

[Tom · Accepted solution]

So those last couple of columns should be numbers. The others it might have trouble figuring out.  Like that value on the first observation with a dash (minus sign?) at the end of the value.

I think all of the unneeded quotes are confusing PROC IMPORT.  Here is an easy way to remove them.

filename sample2 temp;
data _null_;
  infile sample dsd dlm='|' truncover length=len column=col;
  file sample2 dsd dlm='|' ;
  do until(col>len);
    input word :$32767. @;
    put word @;
  end;
  put;
run;

Note that leading/trailing spaces will be removed, but they would have been removed by using delimited input file anyway.

So reading that fixed file again does find that the last two columns are numbers.

Listing of Common Variables with Conflicting Types

Variable       Dataset     Type  Length  Format    Informat

OldValue       WORK.WANT   Char      26  $26.      $26.
               WORK.WANT2  Num        8  BEST12.   BEST32.
COUNT          WORK.WANT   Char       3  $3.       $3.
               WORK.WANT2  Num        8  BEST12.   BEST32.
CC_STAFF_FLAG  WORK.WANT   Char       3  $3.       $3.
               WORK.WANT2  Num        8  BEST12.   BEST32.

Note that Enterprise Guide has its own file import utility that will ingest and file and analyze it and then upload (a possibly modified version of) the file and generate code to read it. So it might not have actually been running PROC IMPORT on your original file.

[BruceTao]

Hi,

Getting close.

Removing the quotes from the source system worked except for one field.

It changed the value to numeric but it should of been a character. Just briefly looking at the field, it might be that most of, if not all the values are numeric. 

How can I change that one field to character?

Thanks

Bruce

[Tom]

---

@BruceTao wrote:

Hi,

Getting close.

Removing the quotes from the source system worked except for one field.

It changed the value to numeric but it should of been a character. Just briefly looking at the field, it might be that most of, if not all the values are numeric. 

How can I change that one field to character?

Thanks

Bruce

---

You cannot force PROC IMPORT to that.  The problem is that the only metadata that a delimited file contains is the header row. There is no place to encode any information about the variable types, lengths, formats, etc.

If you can create an XLSX file instead then SAS could know what data type was used for the cells.

Note that there isn't really any need to use PROC IMPORT to read a delimited file as it is almost as easy to write the data step code to read it as it is to write the code to run PROC IMPORT.  When you do that you will have complete control over how to define the variables.

What I normally do is just copy the header row and turn it into a LENGTH statement.  If you are not sure how to define the variable just make it as a long character string and then examine the results.  You can always just fix the program and re-run it. Then I will add INFORMAT and/or FORMAT statement for those variables that REQUIRE them. Most variables do NOT require either.   But values like DATE, TIME and DATETIME values do need informats to read the text into value properly and FORMATS to display the values as text in a human recognizable way.  In your data you might want to use the TRAILSGN informat to read those values with the negative sign on the end.

data my_data;
  infile sample dsd dlm='|' truncover firstobs=2;
  length 
Action $50 ObjValue $12 User $8 Date 8 Time 8 ChangeID $3 FieldName $32 ShortText $50
OldValue 8 NewValue 8 COUNT 8 CC_STAFF_FLAG 8
  ;
  input Action -- CC_STAFF_FLAG;
  informat date yymmdd. time time. oldvalue newvalue trailsgn.;
  format date yymmdd10. time time8.;
run;

Obs   Action                                               ObjValue      User           Date        Time

 1    Minimum interest rate (interface only) Changed      0023175086    E77319    2017-07-12    11:25:11
 2    Interest Settlement Frequency (interfac) Changed    0023175086    E77319    2017-07-12    11:25:11
 3    Amount Determ. Category (interface) Changed         0023175086    E77319    2017-07-12    11:25:11
 4    Total Monthly Payment Changed                       0023175086    E77319    2017-07-12    11:25:11

      Change    Field                                                 Old        New           CC_STAFF_
Obs     ID       Name     ShortText                                  Value     Value   COUNT      FLAG

 1      U      ZZN40024   Minimum interest rate (interface only)       0     -999.00     1         0
 2      U      ZZN40029   Interest Settlement Frequency (interfac)     .        0.00     1         0
 3      U      ZZN40030   Amount Determ. Category (interface)          .       30.00     1         0
 4      U      ZZN80014   Total Monthly Payment                        0      405.09     1         0

[Reeza]

How quotations marks are interpreted is likely responsible for the different behaviour here. In Viya, how exactly are you importing your data?

[BruceTao]

Hi Tom,
I think you hit the nail on the head with the quotes comment. I think it
uses the pipe to delimit and then it sees the comments and says that these
are all characters. Since I have control of the input file, I will just
remove the quotes completely.

I have to admit that this SAS user forum is so amazing in terms of service.

If I had my way, you would get a raise.

Thanks so much.

Bruce

[Reeza]

Raise? We aren't paid! Unless it says SAS employee under the name, everyone else is a SAS user like yourself 🙂