topic Re: Presenting datasets in SAS Programming

Presenting datasets

johnsaini — Wed, 24 Jun 2015 16:37:48 GMT

COUNTY

1	Texas	Collin
2	Texas	Dallas
3	Georgia	DeKalb

AGE_DISTRIBUTION

1	100	20	40	60
2	10	10	40	50
3	45	100	56	67

AGE_DISTRIBUTION_DESC

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

Requirement 1: Write a SAS program (with ODS) to use the datasets given above to generate HTML and MS EXCEL file that will look like the following (the files must have the 3 tables given below):

County Name	Age Distribution
County Name	Category	Total Number
Collin	< 10 years	100
	Between 10 and 20 years	20
	Between 20 and 40 years	40
	> 40 years	60
Dallas	< 10 years	10
	Between 10 and 20 years	10
	Between 20 and 40 years	40
	> 40 years	50
DeKalb	< 10 years	45
	Between 10 and 20 years	100
	Between 20 and 40 years	56
	> 40 years	67

Category	County
Collin	Dallas	DeKalb
< 10 years	100	10	45
Between 10 and 20 years	20	10	100
Between 20 and 40 years	40	40	56
> 40 years	60	50	67
Total	220	110	268

Category	State
Texas	Georgia
< 10 years	110	45
Between 10 and 20 years	30	100
Between 20 and 40 years	80	56
> 40 years	110	67
Total	330	268

My solution:

data mypro.county;

input county_id state_name $10. county_name $10.;

cards;

1 Texas Collin

2 Texas Dallas

3 Georgia DeKalb

;

run;

data mypro.age_distribution;

input county_id age1 age2 age3 age4;

cards;

1 100 20 40 60

2 10 10 40 50

3 45 100 56 67

;

run;

data mypro.county1;

merge mypro.county mypro.age_distribution;

by county_id;

run;

proc tabulate data=mypro.county1;

class county_name;

var age1 age2 age3 age4;

tables county_name,(age1 age2 age3 age4 all);

run;

The tabulate procedure is not giving the desired result?

Re: Presenting datasets

ballardw — Wed, 24 Jun 2015 20:46:52 GMT

First, print your County data set. You will see that as written that the data was read incorrectly. Hint: Specifying a format on input statement forces the whole width of the format to be used when reading character data.

Second one way to get meaningful text instead of variable names is to associate a label with a variable.

label county_name='County'; Label state_name='State'; label age1='< 10 years';

Another way is to create a single value that has a format to indicate the data source.

Third, All in tabulate as presented in your example is counting County_name and compared to the first table unneeded.

The first table spanning header isn't going to come from proc tabulate without extreme amounts of data manipulation.

I think you might look into Proc Report to generate these reports without completely changing the data structures you have.

Re: Presenting datasets

johnsaini — Thu, 25 Jun 2015 14:45:17 GMT

Hi,

Do I need to merge county and age distribuiton data sets?

Re: Presenting datasets

ballardw — Thu, 25 Jun 2015 15:04:13 GMT

What you did before looks reasonable as to display state and county with age they will need to be in the same data set or else you get involved with a very complicated bit of code, not impossible just likely to be 10 times as much work with high complexity.

Note that starting proc report using syntax such as:

proc report data=anydatasetname; run;

starts a windowing environment that allows picking variables and there rolls as row or columns, statistics, summaries and such.

Re: Presenting datasets

Aexor — Sun, 06 Sep 2020 13:09:52 GMT

@johnsaini @ballardw Could you please help me in this question. I am not getting the desired o/p

Re: Presenting datasets

ballardw — Sun, 06 Sep 2020 20:07:46 GMT

@Aexor

Please start a new thread on the forum. Adding on to a 5 year old thread isn't best practice. Provide example data that you have in the form of a data step and what the appearance should be. If there are any rules about combining elements include them.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.