hi Vince, thanks very much for your help   your code is understandable for a SAS beginner like me. I am going to try it out tomorrow when i get my SAS programm again ( it was gone after the company pc update ). i probably need a few adjustments like you mentioned. cannot wait to find out if it works BR  Dingdang ... View more

Hi Oleg, thanks for your answer. sorry i didnt formulate my question that clearly. I tried to describe my task in another discussion: table editing with (probably) array, call execute and macro. I would be very thankful if you could take another look at the task. BR  Dingdang ... View more

Hi Vince and Astounding, thank you both very much for your answers. Sorry I didn’t come back to you earlier. Was a bit stressed from other tasks. Here I try to formulate my problem again. There are indeed a large number of variables and 3 groups. I am doing a kind of survey. People get a questionary in Excel with a few worksheets and there are variables, which are independent of the groups, which means there are more variables than just name and firstname that should be omitted from the arrays. they are i.e. on the first sheet. and then there come three seperate sheets to the groups, containing the same kind of variables, only belonging to different groups. The variables in the first group are named like “xx_1”, “yy_1” etc. the second group “xx_2” “yy_2” and so on. the first of these three sheets includes the first group, and everyone has to fill in this sheet. In order to reduce the workload for the persons queried, I am putting a variable SameAsPrior2 on the top of the second sheet. if they choose 1, which means the values of the second group are identical to the first group, then they can leave this sheet and go to the third one. If they choose 2, then they have to fill in it. and analogous to the third sheet, they can choose if the values should be identical to one of the first sheets or they have to fill in per hand again. I am not allowed to use macro in Excel, because of security reasons. and because of the large num-ber of variables i am having, i dont want to use any “when, then” function in excel, to avoid any speed problem. At the end of the questionary I have a separate sheet to aggregate all the information filled. For the sheets to the groups I am leaving enough space for the group 2 and group 3. But in some cases they are of course empty. I then collect the workingtables and read the aggregated sheets into SAS. For my analyse I don’t want certain variables to be empty, i.e. the variables with names starting with “xx”. That’s why I want the information correctly copied from other groups, which were filled.  the internal order of the variables doesnt really matter. I hope this time I made my problem clear. And thanks again for your help. BR  Dingdang ... View more

Hi Oleg, I have a similar question again. In my table I have groups of variables like you helped me with before. So it looks like everyone should fill three lists, one to the first group, one to the second and one to the third. In ordner to reduce the workload for them, i am putting in another two variables. On the top of the second list, it would be asked, if the values they fill in for the second group should be the same as the first one. If they choose yes, then they can just leave the second one unfilled and jump to the third list, and will be asked the same question again, if the values should be the same to the first one or to the second one. If they choose, it should be the same as the second one, then they leave the list unfilled again and finish. If they choose no, then they fill the third list per hand. But overall, this way should be able to reduce the work for some persons. BUT the work is left for me. I have then always the first group of variables filled, and answers to two questions. in the worst case, the second and third lists are then empty, and the values i finally got into SAS are missing. How i can copy the values of the first group (or sometimes the second group) into the second group or the third group? I am thinking about using arrays, but not sure how to do it. A possible table would look like this: input Name Firstname Number Hobby1 Adress1 X1 Y1 Z1 SameAsPrior2 Hobby2 Adress2 X2 Y2 Z2 SameAsPrior3 Hobby3 Adress3 X3 Y3 Z3; cards; Lee, Mike, reading, P30, open, closed, open, 1,,,,,,1,,,,,, Frank, Sue, biking, B20, closed, closed, closed, 2, reading, B12, open, closed, closed, 2,,,,,, Mueller Anna, theater, P13, open, open, closed, 2, reading, P21, closed, open, open, 1,,,,,, ; the variable SameAsPrior2 has two values, 1 means yes, same to the first group, 2 means not same to the first group. the varibale SameAsPrior3 has three values, 1 means same to group 1, 2 means same to group 2 and 3 means not same to the first two groups. I hope I made the problem clear. Hope you can help me with this problem again. thanks so much!!! BR  Dingdang ... View more

The goodness of the coefficients depend on your data, the distance measure you use (euclidiean, city-block, correlation etc. ) and the kind of algorithmus that you choose. If you have no idea, how the data is going to look like, most of the people start with hierarchical clustering, especially average-linkage methode. the problem is the speed. it takes relativly longer time. if you have a reasonable guess, then k-means or K-medoids will be better, because they are pretty fast for large data sets. if you want more detailed information about the data sets, then try soft-clustering methode, like Fanny algo. In fast there is no the best methode. All the steps, like how you choose your distance measure, are not trivial. ... View more

cool, this works definitely with the example data. I am going to try that on my real data and see how it goes Thanks again for the great help! BR  Dingdang ... View more

Hi Oleg, thanks for your fast answer. this does almost exactly what i want! The only problem is, i have many of those variables, about 200 of them, not only hobby, adress, tel,..but many more. Is there any way that i can avoid tiping in the variable names one by one with keep? and the same to array definition. many many thanks again! BR  Dingdang ... View more

hi Oleg, thanks for your answer. this is exactly what i am looking for BR  Dingdang ... View more