when I use ( where=(col1 ne ' ') ) ;   data file created is  obs id vars S 1 A 2 2 B 1 Better but not what I need. The part of the code that looks like it is not working is this one: data INSD; set long; mm03a56051S = scan(_name_,-1,"_"); drop _name_ col1; run;     ... View more

I checked the variable as you suggested, and it looks right. Please see below. I have these 3 xxxS variables that I am trying to transpose into one xxxS. As you can see only one option of one obs has data entered. This will be similar throughout. Some will have nothing entered and others will. There is no way for me to have any numeric values in these (due to the way the data is collected). I tried your suggestion and it still shows the variable I am creating xxxS as numbers not characters. Please see outputs below. I can't figure out why. Thanks. obs id name COL1 this becomes my var xxxS 1 A xxxS_1   2 A xxxS_2   3 A xxxS_3 Characters entered  4 B xxxS_1   5 B xxxS_2   6 B xxxS_3     obs id name  var xxxS 1 A xxxS_1 1 2 A xxxS_2 2 3 A xxxS_3 3 4 B xxxS_1 1 5 B xxxS_2 2 6 B xxxS_3 3 ... View more

That is good to know. What I don't understand is why my code (please see below) for transposing the character variables are not working as they do for numeric. I must be doing something wrong. I just can't see it. Thank you. proc transpose data=have out=long ( where=(col1 ne ' ')  * since this is a string var I changed this from 0 to ' '  I also tried this with ne 0 ) ; by id; var xxxxxxS:; run; data want; set long; xxxxxxS = scan(_name_,-1,"_"); drop _name_ col1; run;   ... View more

Sorry about the multiple messages. I read that if I have a mix of numeric and characters I need to transform all of them into characters first. Is that the only way? And would date variables also be transformed into character without giving me issues? The information I found was to use var _all_; But if I am transposing one  by one I was hoping to be able to transpose the characters as characters and dates as dates. Is that possible in this case? ... View more

Understood. I am planning on doing that. But I am not sure if I need to change the transpose, when I use it for string and dates it does not work. I am wondering if I am doing something wrong.I am using the code provided here and adding the string variable names where you see the  numeric variable names. there is probably a change needed for variables that are not numeric? ... View more

yes, of course. Sometimes I am trying to do things differently and forget the basics. I did find the one id that was indeed incorrectly entered. Col2 is then perfect for me to check for these issues. I really appreciate your help. I have another question. How do I transpose string and date variables? I tried to use the code provided but it did not work. I will give an example because it will add complexity. I am using the transpose for all the numeric variables. Some of them, not all, have a corresponding string variable. So for example in the example I gave,  if med 1 is selected a string one will appear. If med 2 is selected a string 2 will appear and so on. So I would need to create a common var String (instead of string 1, string 2 and so on) that would assign each string to each key selected in the Med var.  I would do the same with dates variables that are sometimes asked for the numeric variables the same way I would do the strings. Thank you again. ... View more

Thank you so much ! So the "repeated by values" is not because the data is being stacked (long) and I would have more than one observation for each OBS? It is coming from the original? If so, I would have to find out which one it is, because that would indicate a mistake in my raw data. We are not supposed to have repeated obs for each OBS in the original file. I see only one observation with a value of 1 in column 2 which would mean I only have one repeated Id maybe. Could I use COL2 to find out this quickly? If so, how? Thanks again. ... View more

That is perfect. Thank you Tom. Can I just ignore the var COL2 in my output? I am planning on dropping it, but just wanted to make sure I do not need to use it to check for issues. ... View more

When I transpose each variable and merge I then have the issue that the values of the first var will repeat to fill in blanks and the same will keep happening when I add more variables. I am trying to remember how we do this, to stop filling in blanks. First obs? I am not sure. I would use something like that in my merge statement, right?  The way I have it now you can see the last ID has the same value repeating because it has 2 values for the second var. This is what others were telling me. And I believe it is okay to leave the others as missing. But I need to find out how to prevent the repetition to fill in blanks. obs med living 1 7   2 4   3 8   4 2 2 5 2   6 3 1 6 3 8 ... View more

I see this does not work if I want to add more variables. It overwrites. This is the issue brought up before. So my option would be maybe proc transpose each variable and then merge the files ? ... View more

This is it! Thank you so much. Questions:  1. Can I add other variables to this?  If I am adding other vars (A B C) would this be correct below. 2. what is the vat col2? My output is showing col2 and they are all missing except one obs has a 1. proc transpose data=have out=long ( where=(col1 ne 0) ) ; by obs; var med: A: B: C:; run; data want; set long; med = scan(_name_,-1,"_"); A= scan(_name_,-1,"_"); B = scan(_name_,-1,"_"); C= scan(_name_,-1,"_"); drop _name_ col1; run;   ... View more

Also I can output the other variables that involve more than 50 selection options as their own file. This would allow me to have a smaller (separate) file with the variables that are like med and living(shown here). But I still need to know how to create the new vars, changing the format and how to handle the missing shown. Thank you. ... View more

Oh, I see what you mean. I apologize. So yes, it would have an issue right? Especially when I am adding variables that have 50+ options. So I added a var Living with 1-8 values just like Med. 0 for not selected, 1-8 to the selected ones. OBS Med Living 1 8 2 1   3 2 1 1 2 3 2 2   3 3 1 0 4 1 1 4 2 2 4 3 3 4   5 6 1 1 6 2 2 6 3 0 6 4 0 6 5 0 ... View more

Yes, the others are also select not select, exactly like the med vars here. Thank you. ... View more

5 is not  there because it only had 0's nothing selected. it would look like this. OBS Med 1 8 2 1 2 3 3 1 4 1 4 2 4 3 6 1 6 2 6 3 6 4 6 5 ... View more