Hello Tom,   thanks for your suggestion.   The problem with the missing option is that the count for “all N” then also includes the missing values. But I need a count of all non-missing values. Luckily, my (real) data doesn't have any missing values for "gender", so the column for C will not be excluded from the table. But is there a way to include the missing option and still only count the non-missing values?           ... View more

Hello ballardw,   many thanks for your answer. As you recommended, I will take a look at the Report Writing Interface documentation.   I do not consider a statement like : " I would like to have looks exactly like the table generated on the example data in my previous post. The only problem is that the numbers produced for N are wrong." a clear definition of what is wanted for a report table appearance. As a minimum I can't tell which post. Or which data, since you have been very reluctant to provide any. The example data/table I was referring to is this one:   data have; input exact_location $ icdcategory_1 $ icdcategory_2 $ icdcategory_3 $ gender $ years_spent; datalines; A A1 . C5 m 20 A B4 C5 D6 f 20 A . A1 B4 m 20 B . A2 B4 m . C . C5 A1 . 40 ; data reshape; set have; array r(*) icdcategory: ; do i=1 to dim(r); icdcatname = vname(r[i]); icdcatvalues = r[i]; output; end; keep years_spent gender exact_location icdcatname icdcatvalues; run; proc tabulate data=reshape; class exact_location gender icdcatvalues; var years_spent; table years_spent gender all icdcatvalues all, exact_location; run;   ... View more

Hello ballardw,   the report which I would like to have looks exactly like the table generated on the example data in my previous post. The only problem is that the numbers produced for N are wrong.   What I mean by an affected N is that the total N for "gender" should be 4, as there are only four entries for gender in the example data (3 m and 1 f) , but after performing the reshape step, N for "gender" at location A alone totals already 7 (5 m, 3 f). Same is true for the numerical variable "years_spent" wich also gets increased after the reshape step.   So your addition of a character variable Gender pretty much means that PROC Tabulate is not going to work for the report you want. So if it is not possible to create a table like this with proc tabulate, is there an other method or is it not possible to do this with SAS in general?   Many thanks, Curt      ... View more

Hi mkeintz,   thanks for you suggestion. Unfortunately, the code doesn't seem to work: the row_count always displays a zero and the table  shows only one value of each variable. ... View more

Hello ballardw,   What "numeric variables". Your discussion only mentions class variables Your comment sounds a whole lot like there are other variables that you did not mention at all in your question Yes that's true. The reason for this was that the ICD variables were the only ones I wanted to have reshaped. There are other variables that I would like to include in the table, but I didn't know that their N would be affected by the reshaping of the ICD variables; that is why I didn't mention them before.   Did you read the part of my response that says? Note that if there are other variables that need to be used in this report then you would need to keep them in the reshape step but exactly how may have some details as nothing about them has been discussed. Yes, I read it, but I misunderstood it. I thought you just meant that I need to include all the other variables that I need in addition to that in the keep statement of the reshape step.   Other variables which I also want to display in the table are the numerical one "years_spent" and the categorical one "gender".   I would like a table that looks like this, but with an unaffected N for "years_spent" and "gender":   data have; input exact_location $ icdcategory_1 $ icdcategory_2 $ icdcategory_3 $ gender $ years_spent; datalines; A A1 . C5 m 20 A B4 C5 D6 f 20 A . A1 B4 m 20 B . A2 B4 m . C . C5 A1 . 40 ; data reshape; set have; array r(*) icdcategory: ; do i=1 to dim(r); icdcatname = vname(r[i]); icdcatvalues = r[i]; output; end; keep years_spent gender exact_location icdcatname icdcatvalues; run; proc tabulate data=reshape; class exact_location gender icdcatvalues; var years_spent; table gender years_spent icdcatvalues all, exact_location; run;      ... View more

Hello ballardw, thank you for your help. I just tried out your code and it displayed exactly what I wanted: a total count of all the non-missing values per column and a count of every specific icd-code within a column. I just needed to add “all“ behind “table rowval“ to get a total.   However, I found out that the reshape step has an influence on numeric variables written in the keep statement by increasing their N:   data example; input col $ row1 $ row2 $ row3 $ numvar; datalines; A A1 . C5 1 A B4 C5 D6 2 A . A1 B4 3 B . A2 B4 4 B . C5 A1 5 ; proc means data=example; var numvar; run; data reshape; set example; array r(*) row: ; do i=1 to dim(r); rowname = vname(r[i]); rowVal = r[i]; output; end; keep col rowname rowval numvar; run; /*N of variable numvar gets increased by three times after the reshape step:*/ proc means data=reshape; var numvar; run; This for example results in wrong sums etc. for those numeric variables. Is it possible to do something about it?   Thank you, Curt ... View more

Hi ballardw,   thank you for your answer. Unfortunately, I can't show the data because it is confidential. However, what I can say is that all the row variables describe diagnoses (ICD-Codes) and the column variable describes the exact location from where those diagnoses are from.   So if proc tabulate doesn't work in this case, is there another possibility to display the data in the way I described?   Thank you, Curt ... View more

Great suggestion! Thanks to everyone! ... View more

Thanks for your reply.   I know that it would be better to use the IF STATEMENT instead of the WHERE STATEMENT, however, I want to do  PROC FREQ where listed variables have one of the listed values. So it is not possible to use the IF STATEMENT instead.  ... View more

That was very useful; thanks to everyone, especially to you ballardw for your detailed explanations! ... View more