BookmarkSubscribeRSS Feed
Fluorite | Level 6

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.

Super User

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.


Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 2 in conversation