https://communities.sas.com/t5/New-SAS-User/How-to-streamline-process-of-taking-a-categorical-free-text/m-p/609200#M17840 <P>If this is a one time process and only about mapping 117 values to 35 then doing this "manually" in code appears as the quickes solution to me.&nbsp;</P> <P>Instead of IF/THEN you could create and use a SAS format.</P> Tue, 03 Dec 2019 22:11:25 GMT Patrick 2019-12-03T22:11:25Z https://communities.sas.com/t5/New-SAS-User/How-to-streamline-process-of-taking-a-categorical-free-text/m-p/609166#M17826 <P>Hey all,</P><P>&nbsp;</P><P>I currently attempting to create a new database that is just an improved version of one already in existence. One of the big problems (and massive eyesore) of the old dataset is that a particular variable (genotype) is a free text categorical variable so if you misspelled a diagnosis or accidentally added an extra space it counts that as a new level of that variable.</P><P>&nbsp;</P><P>To paint the picture of how annoying this is and why we need to change it for the new database: This variable has approximately 35 levels that we are interested in but the current database has about 117 unique values for that variable.</P><P>&nbsp;</P><P>I want to consolidate the old diagnoses into the new format (which is a categorical variable that takes a value between 1-13, where a value of 12 allows for a drop-down box to free text if in Redcap). Is there a way to quicken the process of changing the levels so that they don't repeat with weird (and useless) additions or am I about to just write a massive "if-then" statement?</P> Tue, 03 Dec 2019 20:41:23 GMT https://communities.sas.com/t5/New-SAS-User/How-to-streamline-process-of-taking-a-categorical-free-text/m-p/609166#M17826 amail94 2019-12-03T20:41:23Z https://communities.sas.com/t5/New-SAS-User/How-to-streamline-process-of-taking-a-categorical-free-text/m-p/609168#M17827 Does this need to repeatable or is this a one time process? If it's one time, then use Open Refine which is great to help you clean up categories quickly. But otherwise you're stuck writing IF/THEN statements. You could try fuzzy matching but you don't have much data so cleaning it manually is the fastest approach but won't scale though. Tue, 03 Dec 2019 20:43:38 GMT https://communities.sas.com/t5/New-SAS-User/How-to-streamline-process-of-taking-a-categorical-free-text/m-p/609168#M17827 Reeza 2019-12-03T20:43:38Z https://communities.sas.com/t5/New-SAS-User/How-to-streamline-process-of-taking-a-categorical-free-text/m-p/609173#M17830 <P>Ok, I was worried that was probably the better, albeit not quickest, solution. It will be a one-time process (in theory) since once the new database goes live your only choice will be to select a value between 1-13 corresponding to that diagnosis.</P><P>&nbsp;</P><P>When you say fuzzy matching, are you talking about the COMPGED function?</P><P>&nbsp;</P><P>Thanks for your help</P> Tue, 03 Dec 2019 20:49:04 GMT https://communities.sas.com/t5/New-SAS-User/How-to-streamline-process-of-taking-a-categorical-free-text/m-p/609173#M17830 amail94 2019-12-03T20:49:04Z https://communities.sas.com/t5/New-SAS-User/How-to-streamline-process-of-taking-a-categorical-free-text/m-p/609188#M17834 There's more than one way to do fuzzy matching, but compged is a good start. Tue, 03 Dec 2019 21:35:42 GMT https://communities.sas.com/t5/New-SAS-User/How-to-streamline-process-of-taking-a-categorical-free-text/m-p/609188#M17834 Reeza 2019-12-03T21:35:42Z https://communities.sas.com/t5/New-SAS-User/How-to-streamline-process-of-taking-a-categorical-free-text/m-p/609200#M17840 <P>If this is a one time process and only about mapping 117 values to 35 then doing this "manually" in code appears as the quickes solution to me.&nbsp;</P> <P>Instead of IF/THEN you could create and use a SAS format.</P> Tue, 03 Dec 2019 22:11:25 GMT https://communities.sas.com/t5/New-SAS-User/How-to-streamline-process-of-taking-a-categorical-free-text/m-p/609200#M17840 Patrick 2019-12-03T22:11:25Z