I have a data set (.cvs), that I use on a daily basis (includes about 60-75 variables). The source of this data set has gone through some upgrades, and now automatically pulls data into the system. It will not override data, so the system just adds a new iteration (to those questions that allow it) even if it is the exact same data that is already in there. So for each person (row) some of my variables have a string of data separated by commas. I have attached an example excel spreadsheet, showing the data I have and what I want (I work with health data, so this is an example of what my data set looks like). Now, if the unique variables cannot be separated into their own columns, I am okay with having them in the same column. My SAS skills (SAS 9.4) are fairly minimal (proc freqs, some simple SQL, merging, concatenation, sorting, pulling first obs of a string, etc.) I have searched on line and tried the following: data want; set have; Want_date=scan(Have_Date, 1, ' '); do i=2 to countw(Have_Date,' '); word=scan(Have_Date, i, ' '); found=find(Want_date, word, 'it'); if found=0 then Want_date=catx(' ', Want_date, word); end; run; This did not work. But to be honest I wasn't sure what this was exactly doing, so I may not have done it right. When that didn't work I also found this code online and tried it: data want; if not prxId then prxId + prxParse("s/\b(\w{2,})\b(.*)\b(\1\s*)\b/\1\2/io"); set have; Want_date= Have_Date; do i = 1 to 100 until (times=0); call prxChange(prxId,1,Want_date,Want_date,len,trunc,times); end; drop i prxId len trunc times; run; This was close, I ended up with a single date but it was followed by all the commas and the slash marks from the other dates in the field. (example: 05/30/2019,//,//,//,//,//,//,//,//). Again, it could be because I am not sure what the code is all doing, so I wasn't able to correct it to remove the commas and slash marks. I just sent this one example but I have multiple columns that all have this same problem. So any help would be appreciated. Thank you
... View more
