LOL, it's validating to see that this is indeed bad database administrating going on. I can also think of another data warehouse that provides data with these issues (we just asked the data provider to delete the entire variable)...so it's a bit surprising to me that they seem to either be incompetent, or inexperienced at understanding how to correctly CSV files. Thank you so much for your help and insight! ... View more

I copied a section that had less sensitive data for me to replace but I assume it would have been: "MORE DATA pnp14h6 pnp64h29 pnp68h31 pnp66h30 pnp68h31 pnp208h95" As in the beginning quote started before that with more line breaks, blank spaces, etc. But otherwise, thanks so much for the advice on getting back to the database people. I am not familiar with this but will copy and paste what you said to the relevant people! Thanks for linking the macro - that would definitely help with variations of this type of data. ... View more

pnp14h6 pnp64h29 pnp68h31 pnp66h30 pnp68h31 pnp208h95","False","False","False","","","","True","2","1","","True","False","False","False","False","False","False","False","","","","","","pnp46h22 pnp96h45 pnp94h44 pnp232h107","False","","","","","","","","","","","True","" "00000","00000-0000","Name2","Name1","Encounter","Agent","Place A","","False","False","False","False","False","","","","","","False","False","False","False","","","","","","","False","False","","","False","False","False","","","","","","","","",""," ","False","False","False","False","False","False","False","False","False","False","False","False","","","","","","","","","","","","","","True","","data data","","","","","","1iccp38h17 1iccp40h18 1iccp42h19 1iccp44h20 1iccp154h67 1iccp156h68 1icc1318 1iccp211h94 1iccp214h95 1icc1319 ","True","","","","",": pn811 pn812 pn814 pn815 pn816 pn817 pn818 pn819 pn814 pn815 pn816 pn817 pn818 pn819 ","True","False","False","","","False","","","","","","","","","","True","False","","","","","pn1111 pnp78h36 pnp128h59 pn1122 pnp170h78 pnp216h99 pnp260h119 pnp298h135 pnp296h136 pn1152","False","False","False","","","","","","","","","","","","True","","","pnp28h13 pnp30h14 pnp32h15 pnp82h38 pnp84h39 pnp86h40 pnp132h61 pnp134h62 ","False","False","","","True","False","","","","pnp140h65","True","","","","pnp62h28 pnp8h3 pnp160h73 pnp206h94 pnp250h114 pn1752 ","True","","","","","","pnp38h18 pnp34h16 pnp36h17 pnp40h19 pnp42h20 pnp130h60 pnp108h51 1811 pnp88h41 pnp90h42 pnp92h43 pn1818 pnp136h63 pnp138h64 pnp179h82 pnp184h85 pnp186h86 pnp220h101 pnp222h101 pnp224h103 pnp226h104 pnp228h105 pnp230h106 pnp264h121 pnp266h122 pnp268h123 pnp270h124 pnp300h137 pnp302h138 pp304h139 ","False","","","","","","","","","False","","","","","","","","True","","","","pnp12h5 pnp64h29 pnp66h30 pnp68h31 pnp118h54 pnp120h55 pnp14h6 pnp162h74 pnp164h75 pnp208h95 pnp14h6 pnp16h7 pnp70h32 pnp122h56 pnp166h76 pnp252h115 pnp254h116 pnp290h132 pnp290h133 ","False","False","False","","","","True","8","9","","False","False","False","False","False","False","False","False","","","","","","pnp46h22 pnp48h23 pnp50h24 pnp52h25 pn224 pn2242 pnp94h44 pnp98h46 pnp100h47 pnp102h48 pnp104h49 pn2221 pnp142h66 pnp144h67 pnp146h68 pnp148h69 pn2231 pnp188h87 pnp190h88 pnp192h89 pnp194h90 pnp196h91 pnp232h107 pnp236h109 pnp238h110 pnp240h111 pnp272h125 pnp274h126 pnp276h128 pnp306h140 ","False","","","","","","","","","","","True","1iccp38h17 1iccp40h18 1iccp42h19 1iccp44h20 1iccp154h67 1iccp156h68 1icc1318 1iccp211h94 1iccp214h95 1icc1319 " "00000","00000-0000","Name2","Name1","Encounter","Agent","Place A", By import, I use infile, but haven't found a solution to deal with raw data that looks like this as it hasn't come up that much (until now) and I'm always being pressed to run results.  Not everyone understands just how messy raw files can be, and how long it could take to correct the various issues but definitely, STATA might be causing more issues.  I pasted a random portion of the raw file that shows the spaces and quotes that get pushed into breaks. ... View more

Thanks so much. The prefixes could be be pn, 1icc, 2icc, pnp, P/p, H/h in their entirety. I just found a random example in the variable. The digits after the prefix are usually 3 digits but could vary like: pn1052 p270 H124 / pnp270h124 (the two variations someone could enter the same code) The further issue is that since this is hand typed by people, some might accidentally mistype 1icc as 1ic, or icc. The full list of the codes are available in the exact format they should be in and I can create a reference dataset. I like your suggestion of using the list to generate some code to convert the string. For now, I've been trying to find all of the mistyped combinations and 'correct' them. ... View more

Yes, correct. ... View more

I think it's a database they login into.  The database admin people then turn whatever forms/data get collected in that section into .csvs.  These are always extremely messy that I cannot import into SAS and have had to use STATA to deal with the quote stripping and quote binding feature it has.  Thanks for the suggestion, maybe the step between the database data being entered and turned into csv can eliminate some of this?   ... View more

It's from data that's collected by a laptop or ipad, where people have to type the codes.  I believe people are typing them in, and either adding in spaces or not adding in spaces at their whim. ... View more

Thank you for the suggestion with the separate pass through of removing the spaces first.  That seemed to solve the issue.   I have been looking at PRXCHANGE too but haven't found that slick solution yet! ... View more