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
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.
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.
Thank you so so much ballardw. your code worked and i need to do little bit ODS style formats.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.