How to use proc report for the long-version data

[juliajulia]

Hello SAS Experts,

my data is an excel file with 50 records, just like the following:

OrgID	Male	Female	Transgender	Unknown	Age1	Age2	Age3	Age4	Age5
1
2
3
4
5
6
7
8

I want to generate a report looks like this:

	2018
Age group (yr)	N	%
<13
13–24
25-44
45-64
>=65
Subtotal		100.0
Gender
Male
Female
Trans
Subtotal		100.0

the following is the code that i used, but the code can not give me the format that i want. does anyone have idea how to get the above table?

proc sql;
create table test1 as
select
sum (age1) as ageless13
, sum (age2) as age13_24
, sum (age3) as age25_44
, sum (age4) as age45_64
, sum (age5) as age65up
, sum (sum(age1, age2, age3, age4, age5)) as agesub
, calculated ageless13/calculated agesub as age1percent
, calculated age13_24/calculated agesub as age2percent
, calculated age25_44/calculated agesub as age3percent
, calculated age45_64/calculated agesub as age4percent
, calculated age65up/calculated agesub as age5percent
from test_2018;
quit;

[ballardw]

What actual values are in the variables shown? Please don't make us guess what you have.

Unfortunately an awful lot of people create data in Excel or other spreadsheets that are very poor for other uses.

If you data was in the form of

OrgID   Gender  Age

with Gender having values of Male, Female etc and Age an actual numeric value such as 18 then this would be very easy. As it is you will have to do some reshaping of your data and depending what you actually have in your SAS data set this may be minor or complicated.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... 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.

[juliajulia]

Thank you very much. 

my data looks like:

data WORK.TEST;
infile datalines dsd truncover;
input Q9aMale:32. Q9aFem:32. Q9aTrans:32. Q9aGnUnk:32. Q13A1324:32. Q13A2544:32. Q13A4564:32. Q13A65Up:32.;
label Q9aMale="Q9aMale" Q9aFem="Q9aFem" Q9aTrans="Q9aTrans" Q9aGnUnk="Q9aGnUnk" Q13A1324="Q13A1324" Q13A2544="Q13A2544" Q13A4564="Q13A4564" Q13A65Up="Q13A65Up";
datalines;
84 39 0 0 3 41 70 9
148 62 4 0 2 70 127 15
146 86 1 0 10 82 123 18
135 76 0 0 4 95 102 10
377 81 1 0 6 144 275 34
75 15 1 0 16 26 42 7
197 30 3 0 2 68 128 32
218 97 9 18 2 112 193 35
272 245 0 0 9 101 385 14
375 141 0 0 6 75 363 72
;;;;
run;

the values in each row are the total number of people in the hospital. for example the first row is for hospital #1, it has total 84 male, 39 female, 0 trans gender and 0 unknown gender. 3 people with age between 13 to 24, 41 people between age 25 to 44, 70 people between age 45 to 64 and and 9 people 65 and up. the data is installed in an excel file.

I want to use this data to generate a report looks like this:

	2018
Age group (yr)	N	%
13–24
25-44
45-64
>=65
Subtotal		100.0
Gender
Male
Female
Trans
Subtotal		100.0

the following is the code that i used, but the code can not give me the format that i want. does anyone have idea how to get the above table?

proc sql;
create table test1 as
select
sum (age1) as ageless13
, sum (age2) as age13_24
, sum (age3) as age25_44
, sum (age4) as age45_64
, sum (age5) as age65up
, sum (sum(age1, age2, age3, age4, age5)) as agesub
, calculated ageless13/calculated agesub as age1percent
, calculated age13_24/calculated agesub as age2percent
, calculated age25_44/calculated agesub as age3percent
, calculated age45_64/calculated agesub as age4percent
, calculated age65up/calculated agesub as age5percent
from test_2018;
quit;

[ballardw]

---

@juliajulia wrote:

Thank you very much. 

my data looks like:

data WORK.TEST;
infile datalines dsd truncover;
input Q9aMale:32. Q9aFem:32. Q9aTrans:32. Q9aGnUnk:32. Q13A1324:32. Q13A2544:32. Q13A4564:32. Q13A65Up:32.;
label Q9aMale="Q9aMale" Q9aFem="Q9aFem" Q9aTrans="Q9aTrans" Q9aGnUnk="Q9aGnUnk" Q13A1324="Q13A1324" Q13A2544="Q13A2544" Q13A4564="Q13A4564" Q13A65Up="Q13A65Up";
datalines;
84 39 0 0 3 41 70 9
148 62 4 0 2 70 127 15
146 86 1 0 10 82 123 18
135 76 0 0 4 95 102 10
377 81 1 0 6 144 275 34
75 15 1 0 16 26 42 7
197 30 3 0 2 68 128 32
218 97 9 18 2 112 193 35
272 245 0 0 9 101 385 14
375 141 0 0 6 75 363 72
;;;;
run;

the values in each row are the total number of people in the hospital. for example the first row is for hospital #1, it has total 84 male, 39 female, 0 trans gender and 0 unknown gender. 3 people with age between 13 to 24, 41 people between age 25 to 44, 70 people between age 45 to 64 and and 9 people 65 and up. the data is installed in an excel file.

I want to use this data to generate a report looks like this:

	2018
Age group (yr)	N	%
13–24
25-44
45-64
>=65
Subtotal		100.0
Gender
Male
Female
Trans
Subtotal		100.0

the following is the code that i used, but the code can not give me the format that i want. does anyone have idea how to get the above table?

proc sql;
create table test1 as
select
sum (age1) as ageless13
, sum (age2) as age13_24
, sum (age3) as age25_44
, sum (age4) as age45_64
, sum (age5) as age65up
, sum (sum(age1, age2, age3, age4, age5)) as agesub
, calculated ageless13/calculated agesub as age1percent
, calculated age13_24/calculated agesub as age2percent
, calculated age25_44/calculated agesub as age3percent
, calculated age45_64/calculated agesub as age4percent
, calculated age65up/calculated agesub as age5percent
from test_2018;
quit;

---

How about showing what is in your data set Test_2018 instead of that data step that has serious problems, such as apparently no "age less than 13" variable?

And pick a desired output and don't change it. The first want had a "<13", the second doesn't.