Import csv file with | delimiter

[Addison]

I recently received a csv file from other hospital and in the csv file they merge all the lab name into one column and separate with | as delimiter.

 like below,

MRN

FIN

Lab Date

Lab Name

Lab Value

12345

567

12345

91720

10/14/2004|10/14/2004|10/14/2004|10/14/2004|10/14/2004|10/14/2004|10/14/2004|10/14/2004|10/14/2004|10/14/2004|10/14/2004|10/14/2004|10/14/2004|10/14/2004|10/14/2004|10/14/2004|10/14/2004|10/14/2004|10/14/2004|10/14/2004|10/14/2004|10/14/2004

Absolute Basophils|% Basophils|Absolute Eosinophils|% Eosinophils|Hematocrit|Hemoglobin|Absolute Lymphs|% Lymphs|MCH Blood|MCHC Blood|MCV Blood|Absolute Monos|% Monos|MPV|Absolute Neuts|% Neuts|Platelet Count|RBC|RBC Morphology|RDW|WBC|Slide No.

.005|0.1|.239|2.6|33.5|11.1|2.27|24.6|24.1|33.1|73|.564|6.1|9.3|6.14|66.6|328|4.60|AUTO ASSESSMENT OF MORPHOLOGY UNREMARKABLE|14.5|9.2|143

I try to use proc import to read the original file however it didn't bring the entire records to SAS.

Can anyone suggest me the right coding to import this data?

Thank you.

[ballardw]

Post some example data into a code box opened with the forum's {I} or "running man" from the delimited file NOT anything opened with a spreadsheet program as they will often reformat some columns of a CSV file. Use a simple text program like NOTEPAD or similar. If there is any sensitive data such as names or such replace those with values like "xxxx" or "9999" so we don't see the values.

Second, post the code you used to read the file.

Third, describe what you expect the final data set to look like for other use.

Likely which ever method you used did not provide a wide enough field for some of the values.

Because someone gave you data in an unfriendly layout you will need to split the values out. 

Most likely you will have to be able to tell use what the maximum number of dates/ lab names/ values will occur on a single row to get things to line up correctly.