merging tables

epr — Thu, 11 Apr 2013 19:18:41 GMT

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

Re: merging tables

ballardw — Thu, 11 Apr 2013 19:47:30 GMT

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.

Re: merging tables

epr — Thu, 11 Apr 2013 23:34:13 GMT

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

topic Re: merging tables in SAS Programming

merging tables

Re: merging tables

Re: merging tables