hierarchical data

[cleokatt]

Hi, 

I have a fiction dataset that I've created like this

data countries;
    length country $20 country_1 $50;
    input country $ country_1 $;
    datalines;
AE United Arab Emirates
AR Argentina
AU Australia
Africa Africa
Asia Asia
CA Canada
CH Schweiz
CN China
DE Germany
ET Etiopia
EU Europe
FR France
GB United Kingdom
GM Gambia
KR Republic of Korea
KZ Kazakstan
Latin_America_31 Latin America-31
Middle_East Middle East
NO Norway
NP Nepal
NZ New Zealand
Non_EU_Europe Non-EU Europe
PE Peru
PH Philippines
RU Russia
TR Turkey
UA Ukraine
World World
;
run;

data countries_extended;
    set countries;
    do i = 1 to ceil(ranuni(0) * 100); /* Upprepa varje land slumpmässigt upp till 100 gånger */
        output;
    end;
    drop i;
run;

and I have SAS 9.4 - which mean it can't take union all, and such.

How do I create a variable a variable hierarchy, beacuse now it is mixed in col=country. 

I've read that you can self join in this case, but the output was bad.. Which I tried to do, but it didn't went well. Can someone please help me out?

[Reeza]

What do you want the output to look like?

[Tom]

Without a better description of what you want I don't think we can help you.

Also note that your first data step cannot work as is.  It will only read the first word from values like United Arab Emirates .

Also this statement makes no sense to me:

 I have SAS 9.4 - which mean it can't take union all, and such.

[Tom]

Since you seem to be talking about COUNTRIES let's posit a GUESS about what you are talking about.  Perhaps you want the countries to be organized into regions?  So perhaps your dataset should look something like this:

data have;
  length region $20 country_cd $2 country_name $50;
  infile cards truncover;
  input country_cd country_name & ;
  value=rand('integer',10);
  if country_cd=' ' then region=country_name;
  else output;
  retain region;
cards; 
. Australia
AU Australia
NZ New Zealand
. Africa
ET Etiopia
GM Gambia
. Asia
CN China
KR Republic of Korea
KZ Kazakstan
NP Nepal
PH Philippines
.  Europe
CH Schweiz
DE Germany
FR France
GB United Kingdom
NO Norway
.  Latin America
AR Argentina
PE Peru
.  Middle East
AE United Arab Emirates
.  Non-EU Europe
RU Russia
TR Turkey
UA Ukraine
.  North America
CA Canada
US United State
;

Which you can summarize easily using PROC SUMMARY.  For sake of demonstration let's find the SUM of the arbitrary VALUE variable created in the example HAVE dataset.

proc summary data=have ;
 class region country_cd country_name ;
 types (0 region region*country_cd*country_name;
 var value;
 output out=want sum= ;
run;

Results

                        country_
Obs    region              cd       country_name            _TYPE_    _FREQ_    value

  1                                                            0        22       100
  2    Africa                                                  4         2        16
  3    Asia                                                    4         5        22
  4    Australia                                               4         2         8
  5    Europe                                                  4         5        27
  6    Latin America                                           4         2        11
  7    Middle East                                             4         1         1
  8    Non-EU Europe                                           4         3        10
  9    North America                                           4         2         5
 10    Africa              ET       Etiopia                    7         1        10
 11    Africa              GM       Gambia                     7         1         6
 12    Asia                CN       China                      7         1         2
 13    Asia                KR       Republic of Korea          7         1         6
 14    Asia                KZ       Kazakstan                  7         1         1
 15    Asia                NP       Nepal                      7         1         6
 16    Asia                PH       Philippines                7         1         7
 17    Australia           AU       Australia                  7         1         2
 18    Australia           NZ       New Zealand                7         1         6
 19    Europe              CH       Schweiz                    7         1         8
 20    Europe              DE       Germany                    7         1         6
 21    Europe              FR       France                     7         1         8
 22    Europe              GB       United Kingdom             7         1         4
 23    Europe              NO       Norway                     7         1         1
 24    Latin America       AR       Argentina                  7         1         2
 25    Latin America       PE       Peru                       7         1         9
 26    Middle East         AE       United Arab Emirates       7         1         1
 27    Non-EU Europe       RU       Russia                     7         1         1
 28    Non-EU Europe       TR       Turkey                     7         1         2
 29    Non-EU Europe       UA       Ukraine                    7         1         7
 30    North America       CA       Canada                     7         1         4
 31    North America       US       United State               7         1         1

So we can see there are 22 countries and the sum of VALUE over all of them is 100.  Of those 100 there were 16 from AFRICA (10 from Etiopia and 6 from  Gambia).

[cleokatt]

Thank you so much, but how about the highest hierarchy? i.e "World" how would I handle that?

[ballardw]

---

@cleokatt wrote:

Thank you so much, but how about the highest hierarchy? i.e "World" how would I handle that?

---

In the output _type_=0 will be the summary for ALL observations. If that is your "world" that is the answer.