Dear Reeza, mi idea is to join the two datasets (have) and (have2) to get the dataset WANT, could you write the all code to see it. Thanks. ... View more

Dear Urvish Shah...still is not right... there appeat a have1 in the code, and the family HOUSE is in the end of the dataset, and need to be after the first subfamily, after the row (red).....could you fix this? ... View more

Hi Tom, if The families have the same Freqvlaue, for example FOOD, DRINK =5, the code it doesn.t work right, and the level variable shows something like: level 5FOOD 5 FOOD 5 FOOD 5DRINK 5DRINK 5DRINK They put two categories together. ... View more

Finally , I ridd off the 99999, by max(nsoc) as freqitem in (FAMILIES category), and the numbers 1,2,3,4,5...still dont know why you ise that. You code is brilliant. Thank you very much for all your help.  V proc sql noprint ;   create table display as      select freqvalue, freqitem, flag           , item as level           , intensity           , nitem as count      from prewant   union      select freqvalue, max(nsoc) as freqitem, flag           , upcase(value) as level           , intensity           , nsoc as count   /* This is the value that makes no sense */      from prewant   order by freqvalue descending          , freqitem descending          , flag descending ; quit; ... View more

OK, I understood the 99999, but the by group 1,2,3,4,5 ? Thanks. ... View more

My apologize Tom, I  forgot the counting variable for FOOD and DRINK, I will called nsoc, and I modify your code, and now It get sense. Btw, in your code, I dont understand 99999, and group by 1,2,3,4,5 Would you like to explain me it. Thanks. dataset HAVE:    nvalue    nintensity    nitem    nsoc    value    intensity    item         flag             2           3          1        5     DRINK     high        orange         3             2           2          0        3     DRINK     medium      orange         2             .           1          2        2     DRINK     low         green          1             .           3          0        5     DRINK     high        green          3             .           2          0        3     DRINK     medium      green          2             5           2          3        4     FOOD      low         cheese         1             5           4          0        2     FOOD      high        cheese         3             .           1          4        3     FOOD      medium      meat           2             5           2          4        4     FOOD      low         meat           1             .           4          0        2     FOOD      high        meat           3             5           1          0        3     FOOD      medium      vegetable      2 The dataset would be: proc sql noprint ;   create table display as      select freqvalue, freqitem, flag           , item as level           , intensity           , nitem as count      from prewant   union      select freqvalue,99999 as freqitem, flag           , upcase(value) as level           , intensity           , nsoc as count   /* it makes sense now */      from prewant group by 1,2,3,4,5   order by freqvalue descending          , freqitem descending          , flag descending ; quit; ... View more

OK Tom, tomorrow I will define better my problem. Thank you for your time. ... View more

Hi Tom, yes, forget about if the values of nitems are right or wrong, doesn't mattter (it become of a summary process that I just solved from the RAW of data, and I just got this numbers as the final count.) then.. my final dataset WANT will be something like: level        intensity   nitem FOOD      high        x FOOD      medium   x FOOD      low         x meat        high        x    meat        medium   x   meat        low         x    cheese    high         x  cheese    low          x  vegetable Medium   x  DRINK     high        x  DRINK     medium  x DRINK     low         x green     high         x green     medium    x green     low          x  orange   high         x  orange   medium   x Then, STEP 1) Remember my dataset PREWANT:             freqvalue    value    flag    item         intensity    nitem    freqitem                 5        FOOD       3     meat          high          0          4                 5        FOOD       2     meat          medium        4          4                 5        FOOD       1     meat          low           4          4                 5        FOOD       3     cheese        high          0          3                 5        FOOD       1     cheese        low           3          3                 5        FOOD       2     vegetable     medium        0          0                 2        DRINK      3     green         high          0          2                 2        DRINK      2     green         medium        0          2                 2        DRINK      1     green         low           2          2                 2        DRINK      3     orange        high          1          1                 2        DRINK      2     orange        medium        0          1 Doing some transformation: data base_want (keep=level); set prewant; by descending freqvalue descending freqitem; if first.freqvalue then do; level=upcase(VALUE); count=nitem;          output; end; if first.freqitem then do; level=item; count=nitem; output; end; run; STEP 2) I get this one, called BASE_WANT:                                             level                                            FOOD                                             meat                                             cheese                                             vegetable                                             DRINK                                             green                                             orange Mi idea is,  combining this dataset 2) BASE_WANT with the previous datase t1 )  PREWANT to get the finalk dataset 3)  WANT. I think is is more clear now, btw  (Thank you for your big interest)....I hope you can help me with this last step 3,  FROM STEP 1 AND 2 , I still dont get it. ... View more

Hi Tom, my apologize if I am not been clear here. Nitem, Nvalue is just a proper  summary of number of subjects. The missing values, is because no subjects. This 2 you mean from PREWANT dataset?....5        FOOD      2     meat          medium          4 it is a flag variable...corresponding to the medium intensity. I  get that from manipulating my original dataset have, before use it to PROC SQL: data have;   set have;   if intensity="high" then flag=3;   else if intensity="medium" then flag=2;   else flag=1; run; This dataset finally go to the PROC SQL, I did that because with : descending frequency by this flag variable, I obtain  the descending frequency in that order (High>Medium>Low) The task of this problem is the SORTING following 4 rules: I am not interesting to summarize anything, only to sorting out family (FOOD/DRINKS) and subfamily (items corresponding to every family) by descending frequency, and finally sorting out by (high>medium>low). I hope it is more clear now. ... View more

at least I found the first step (first sorting) of my dataset WANT...I used three PROC SQL, still thinking that is possible to do it with only one PROC SQL, but i dounno how...the thing is, that this works better...I need to think to get last final dataset WANT. proc sql noprint; create table fillvalue as select max(nvalue) as freqvalue,value,flag,item,intensity from have group by value order by freqvalue descending,flag descending; quit; proc sql noprint; create table fillitem as select max(nitem) as freqitem,value,flag,item,intensity from have group by value,item order by freqitem descending,value,flag descending; quit; proc sql noprint; create table prewant as select a.*,b.freqitem from fillvalue a left join fillitem b on a.value=b.value and a.item=b.item and a.flag=b.flag order by freqvalue descending,freqitem descending,flag descending; quit; PREWANT dataset:   freqvalue    value    flag    item         intensity    freqitem                      5        FOOD       3     meat          high            4                      5        FOOD       2     meat          medium          4                      5        FOOD       1     meat          low             4                      5        FOOD       3     cheese        high            3                      5        FOOD       1     cheese        low             3                      5        FOOD       2     vegetable     medium          0                      2        DRINK      3     green         high            2                      2        DRINK      2     green         medium          2                      2        DRINK      1     green         low             2                      2        DRINK      3     orange        high            1                      2        DRINK      2     orange        medium          1 Finallly looking for this dataset WANT: FOOD      high       4 FOOD      medium  1 FOOD      low        2 meat        high         4   meat        medium   1   meat        low          2   cheese    high        4   cheese    low         2   vegetable Medium  1   DRINK     high         3  DRINK     medium  2 DRINK     low        1 green     high        3   green     medium  2   green     low         1   orange   high        3   orange   medium   2 ... View more

Thanks Tom, I still thinking  need to be a query and a  subquery, because two queries  that you mention will add  more records that I really need I think. ... View more

sorry, I missed the dataset with the flag variable, and the BY group (value,item) rather than item, to generate the above dataset: data have;   set have;   if intensity="high" then flag=3;   else if intensity="medium" then flag=2;   else flag=1; run; proc sql noprint; create table fillvalitem as select max(nvalue) as freqvalue, max(nitem) as freqitem, value, flag, intensity, nintensity,item,nitem from have group by value, item order by freqvalue descending, freqitem descending,flag descending; run; ... View more

Hi guys, thank you very much for your contribution, but rather than specify the categories, I would like to do something like this: because in my real problem I have 50 Families, and every individual family have 50 subfamilies... Then, I am trying to do something like MAX() function as Tom Suggest: proc sql noprint; create table fillvalitem as select max(nvalue) as freqvalue, max(nitem) as freqitem, value, flag, intensity, nintensity,item,nitem from have group by item order by freqvalue descending, freqitem descending,flag descending; run; But still is not right, for example DRINK is missing the value 2, and is not sorting out in the descending order, should be 2, and 1, I think the idea is to make a first query of VALUE (group by value), and subquery of ITEM (group by value,item), but still need to think How I can do it. If you can help me with this: Thanks.           5            4       FOOD       3      high             4        meat           .          5            4       FOOD       2      medium           1        meat           4          5            4       FOOD       1      low              2        meat           4          5            3       FOOD       3      high             4        cheese         .          5            3       FOOD       1      low              2        cheese         3          5            .       FOOD       2      medium           1        vegetable      .          2            1       DRINK      3      high             3        orange         1          2            1       DRINK      2      medium           2        orange         .          .            2       DRINK      3      high             3        green          .          .            2       DRINK      2      medium           2        green          .          .            2       DRINK      1      low              1        green          2 ... View more

Thanks Arthur for this, you right the 4 value is the right one. Any possibility to do it using PROC SQL using subqueries? I am thinking about it too. It would be great. Thanks. ... View more