Getting wrong output while importing a csv file with | (Pipe) delimited

[Vimal0808]

Hi Team,

We were trying to import a csv file with | (pipe) delimited, but after importing either the columns are getting shifted to next column or its populating as blank. Please find the samples data and code below. Please help us to resolve the issue with your suggestion.

Your help will be highly appreciated.

CSV file:

101|}|Test 1|0}||Last
101|}|Test Line|0}||Last
101|}|Test|0}|12|Last
101|}|Test Other|0}|12|Last

Code using to extract the data:

Data Test;
Infile '' DSD DLM="|" Truncover;
Input ID : 3. TestZD1. Name : $20. TestZD2 ZD2. Last : $5.;
Run;

Values are shifting to next variable if using ZDw. informats.

Not able to use the colon (:) operator while extracting into ZDw. informat. & getting warning messages if using the same.

WARNING 151-185: List input is incompatible with the specified binary informat. Formatted input will be used.

[s_lassen · Accepted solution]

The ZD. informat is not compatible with the list format. You also have another problem, namely that you actually have 3, not 2 zoned decimal variables, one of which if occasionally missing (the "||" parts of your shown input).

To get around that I would suggest to read the zoned decimals into temporary text variables, and then parse them, like this:

Data Test;
Infile cards DSD DLM="|" Truncover;
length _temp1-_temp3 $2;
Input ID : 3. _temp1  Name : $20. _temp2 _temp3 Last : $5.;
testZD1=input(_temp1,?? zd1.);
testZD2=input(_temp2,?? zd2.);
testZD3=input(_temp3,?? zd2.);
drop _:;
cards;
101|}|Test 1|0}||Last
101|}|Test Line|0}||Last
101|}|Test|0}|12|Last
101|}|Test Other|0}|12|Last
;run;

It may be possible to read the zoned decimals directly, but only if they are fixed length, and the missing values have length 0.

[Vimal0808]

Thank you for your help!!

[Ksharp]

Why not use SCAN() ?

Data Test2;
input;
ID=input(scan(_infile_,1,'|','mq'),best.); 
TestZD1=input(scan(_infile_,2,'|','mq'),zd1.); 
length name $ 200;
Name=scan(_infile_,3,'|','mq'); 
TestZD2=input(scan(_infile_,4,'|','mq'),zd2.); 
TestZD3=input(scan(_infile_,5,'|','mq'),zd2.); 
length Last $ 200;
Last=scan(_infile_,6,'|','mq'); 
cards;
101|}|Test 1|0}||Last
101|}|Test Line|0}||Last
101|}|Test|0}|12|Last
101|}|Test Other|0}|12|Last
;run;