topic Re: Techniques to use to aggregated names and terms into common classifications in SAS Programming https://communities.sas.com/t5/SAS-Programming/Techniques-to-use-to-aggregated-names-and-terms-into-common/m-p/749946#M235794 <P>Have a look at OpenRefine (AKA Google Refine). A free tool for dealing with messy data.</P> Wed, 23 Jun 2021 18:47:49 GMT PGStats 2021-06-23T18:47:49Z Techniques to use to aggregated names and terms into common classifications https://communities.sas.com/t5/SAS-Programming/Techniques-to-use-to-aggregated-names-and-terms-into-common/m-p/749935#M235785 <P>What I need to do is group similar occupations / places of employment together in a contact tracing dataset.</P> <P>&nbsp;</P> <P>I have 124,803 rows and the field I want to "scrape" is Place of Employment.&nbsp; What I am trying to do is get a picture of the impact of covid-19 on contacts from an "economic" POV.&nbsp; I want to group together like work / occupations and then label them as one might see in Bureau of Labor Stats e.g., finance, trade, manufacturing, services and so on.&nbsp; Then hopefully the development team can incorporate these occupational titles into subsequent surveys and make it easier for work / employment analyses in the future.</P> <P>&nbsp;</P> <P>As usual not every obs has&nbsp; response.&nbsp; A small example of some of the responses include</P> <P>&nbsp;</P> <P>row&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Place of Employment</P> <P>&nbsp;</P> <P>10000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; unemployed</P> <P>&nbsp;</P> <P>10210&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; XYZ elementary school</P> <P>&nbsp;</P> <P>11800&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Seven - 11</P> <P>&nbsp;</P> <P>23453&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; retired</P> <P>&nbsp;</P> <P>86754&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Tri-state aviation</P> <P>&nbsp;</P> <P>100256&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; student</P> <P>&nbsp;</P> <P>111876&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; City of Richland</P> <P>&nbsp;</P> <P>123245&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; St. Randall's Hospital</P> <P>&nbsp;</P> <P>and so.&nbsp; As I mentioned there there are a lot of missing /&nbsp; no responses. Of the 124,803 obs there are around</P> <P>35,000 that have something included.&nbsp; So this will be a somewhat tedious to crawl through even 35,000 plus obs but it is a needed data cleaning exercise. Hopefully it will help provide a little more information&nbsp; / knowledge for stakeholders and interested parties.</P> <P>&nbsp;</P> <P>My question is: Are there any SAS tips, techniques, coding, and data tricks that could make the "scraping / aggregation" not quite as tedious but more accurate in the end?&nbsp;</P> <P>&nbsp;</P> <P>Thank you for any ideas, techniques, or processes in advance.</P> <P>&nbsp;</P> <P>wklierman</P> Wed, 23 Jun 2021 18:12:47 GMT https://communities.sas.com/t5/SAS-Programming/Techniques-to-use-to-aggregated-names-and-terms-into-common/m-p/749935#M235785 wlierman 2021-06-23T18:12:47Z Re: Techniques to use to aggregated names and terms into common classifications https://communities.sas.com/t5/SAS-Programming/Techniques-to-use-to-aggregated-names-and-terms-into-common/m-p/749946#M235794 <P>Have a look at OpenRefine (AKA Google Refine). A free tool for dealing with messy data.</P> Wed, 23 Jun 2021 18:47:49 GMT https://communities.sas.com/t5/SAS-Programming/Techniques-to-use-to-aggregated-names-and-terms-into-common/m-p/749946#M235794 PGStats 2021-06-23T18:47:49Z Re: Techniques to use to aggregated names and terms into common classifications https://communities.sas.com/t5/SAS-Programming/Techniques-to-use-to-aggregated-names-and-terms-into-common/m-p/750055#M235856 Thank you for the suggestion. From what I can see it isn't what I need. It isn't so much that the data is messy - the data is what it is. This seems like it will take more of "brute force" approach (a piece-meal approach) with a lot of sub-setting If / then statements. Maybe some type of 'fuzzy-matching' in addition to spedis or soundex.<BR /><BR />I will keep looking. Thanks for the OpenRefine suggestion.<BR /><BR />wklierman Wed, 23 Jun 2021 23:31:32 GMT https://communities.sas.com/t5/SAS-Programming/Techniques-to-use-to-aggregated-names-and-terms-into-common/m-p/750055#M235856 wlierman 2021-06-23T23:31:32Z