merging tables

[epr]

Hello All,

I need need some help merging three tables here and generating ODS report. This is just a practice work.

As a new learner i am able to merge first two tables using by county_id but third table data is arranged horizontally , i tried different ways but did not get output.

Table1

Obs county_id state_name  county_name

1         1              texas                     collin

2         2               texas                    Dallas

3         3            Georgia                 Dekalb     

Table2

Obs county_id Age_0_10 Age_10_20 Age_20_40 Age_40_plus

1           1                100           20                     40                          60                    

2           2                10             10                     40                          50                         

3           3                45           100                     56                          67  

Table3

AGE_DISTRIBUTION_DESC

CATEGORY_NAME      CATEGORY_DESCRIPTION

     AGE_0_10                   < 10 years                                                                               

        AGE_10_20           Between 10 and 20 years         

     AGE_20_40                Between 20 and 40 years                   

     AGE_40_PLUS           > 40 years

need to write ods report like this

                                             Age distribution

county name                      category                                           Total number

collin                                    <10years                                                  100

                                              Between 10 and 20 years                    20

                                              Between 20 and 30 years                    40    

                                           >40years                                                    60  

Dallas                                  <10years                                                  10

                                              Between 10 and 20 years                    10

                                              Between 20 and 30 years                    40    

                                           >40years                                                    50  

Dekalb                                 <10years                                                  45

                                              Between 10 and 20 years                    100

                                              Between 20 and 30 years                    56    

                                           >40years                                                    67

The report should be generated in html and excel format and macro variable  must be used to determine whether to produce HTML output or MS-Excel output.

Thank you

[ballardw · Accepted solution]

Your table 3 doesn't actually contain any data that needs to be combined with the merged data sets one and two. You can add the information as Labels to the variables.

Assuming you did something like:

Data merged;

     merge data1 data2;

     by county_id;

/* add the following*/

     label

     AGE_0_10            =       "< 10 years"                                                                               

        AGE_10_20       =    "Between 10 and 20 years"         

     AGE_20_40         =       "Between 20 and 40 years"                   

     AGE_40_PLUS    =      " > 40 years"

   ;

The ods output table can be generated with something like

Proc tabulate data=merged;

     class county_id;

     var

     AGE_0_10   AGE_10_20  AGE_20_40   AGE_40_PLUS ;

     table county_Id="County name"*(AGE_0_10   AGE_10_20  AGE_20_40   AGE_40_PLUS),

             sum="Total number"*f=f6.0;

run;

Before getting into macros make sure you can generate the output desired without a macro. Also, you will need some other bits, like the output location to write the output and ODS style options for desired appearance.

[epr]

Thank you so so much ballardw. your code worked and i need to do little bit ODS style formats.