topic Re: Analyzing aggregate data using proc SQL in New SAS User

Analyzing aggregate data using proc SQL

MaxRoy — Wed, 10 Jul 2019 13:22:17 GMT

Hello there,

I'm currently analyzing aggregate data using proc SQL and I've encountered an issue.

Is there a way to look at a crosstab with a repetitive row value as a column.

e.g. Dataset:

OBS REGION YEAR SEX COUNT

1 1 2016 M 12

2 2 2016 M 5

3 1 2017 M 10

4 2 2017 M 8

5 1 2018 M 6

6 2 2018 M 15

... ... ... ... ...

I would like to create a table of male count by region and year. However I would like to have the year as a column to get something like this:

COUNT

REGION 2016 2017 2018

1 12 10 6

2 5 8 15

Thank you!

Re: Analyzing aggregate data using proc SQL

PeterClemmensen — Wed, 10 Jul 2019 13:34:53 GMT

Welcome to the SAS Community 🙂

Use PROC FREQ and do something like this

data have;
input OBS REGION YEAR SEX $ COUNT;
datalines;
1 1 2016 M 12
2 2 2016 M 5
3 1 2017 M 10
4 2 2017 M 8
5 1 2018 M 6
6 2 2018 M 15
;

proc freq data=have;
    tables region*year;
    weight count;
run;

Re: Analyzing aggregate data using proc SQL

aaronh — Wed, 10 Jul 2019 15:42:04 GMT

Hello,

There might be a lot of ways to achieve what you need here. What I do is broken down into 3 steps.

**Step 1. proc sql group **;

Group by region and year, since you are interested in the count for each region by year.

proc sql;
create table aggregate as
select region, year, count 
   from dataset
where sex='M'
group by region, year;
quit;

**Step 2. proc transpose **;

proc transpose data=aggregate  out=aggregate2;
var count;
id year;
by region;
run;

**Step 3. data-step clean-up **;

data want;
set aggregate2;
drop _NAME_;
rename
    _2016 = cnt_2016
    _2017 = cnt_2017
    _2018 = cnt_2018
......
;
run;

The dataset named 'want' should be what you are looking for.

Re: Analyzing aggregate data using proc SQL

Ksharp — Thu, 11 Jul 2019 13:22:06 GMT

data have;
input OBS REGION YEAR SEX $ COUNT;
datalines;
1 1 2016 M 12
2 2 2016 M 5
3 1 2017 M 10
4 2 2017 M 8
5 1 2018 M 6
6 2 2018 M 15
;
proc tabulate data=have;
class region year;
var count;
table region=' ',count*year=' '/box='REGION' ;
keylabel sum=' ';
run;

Re: Analyzing aggregate data using proc SQL

MaxRoy — Thu, 11 Jul 2019 13:37:01 GMT

Thank you for your solution. Do you know if I can still use this function if I want to make a crosstab with more than two variables? I've tried it and I get an error.

Ultimately, what I would like to do is a table like this:

2016 2017 2018

REGION MALE FEMALE MALE FEMALE MALE FEMALE

1 12 ... 10 ... 6 ...

2 5 ... 8 ... 15 ...

Thank you again.

Re: Analyzing aggregate data using proc SQL

Ksharp — Thu, 11 Jul 2019 13:41:19 GMT

Sure.

data have;
input OBS REGION YEAR SEX $ COUNT;
datalines;
1 1 2016 M 12
2 2 2016 M 5
3 1 2017 M 10
4 2 2017 M 8
5 1 2018 M 6
6 2 2018 M 15
;
proc tabulate data=have;
class region year sex;
var count;
table region=' ',year=' '*sex=' '*count=' '/box='REGION' ;
keylabel sum=' ';
run;

Re: Analyzing aggregate data using proc SQL

MaxRoy — Thu, 11 Jul 2019 13:46:41 GMT

But that would only work with record-level data (where each row equals one individual). I'm using aggregated data.

So my database looks like this:

OBS REGION YEAR MALE_Count FEMALE_Count TOTAL

1 1 2016 12 5 17

2 2 2016 15 7 22

3 1 2017 10 4 14

4 2 2017 8 4 12

5 1 2018 6 5 11

6 2 2018 15 10 25

It was probably unclear in my first question.

Thanks

Re: Analyzing aggregate data using proc SQL

aaronh — Thu, 11 Jul 2019 14:53:44 GMT

Then maybe consider a single proc transpose:

*Assuming you only care about the male counts*;

proc transpose  data=dataset  out=want;
 var male_count;
 id year;
 by region;
run; 

***Rename variables and delete _NAME_;
proc datasets library=work;
modify want;
rename _2016 = cnt_2016;
rename _2017 = cnt_2017;
rename _2018 = cnt_2018;

Re: Analyzing aggregate data using proc SQL

Ksharp — Fri, 12 Jul 2019 11:53:57 GMT

data have;
input OBS     REGION     YEAR      MALE     FEMALE      TOTAL;
cards;
  1              1             2016              12                        5                        17
  2              2             2016              15                        7                        22
  3              1             2017              10                        4                        14
  4              2             2017               8                         4                        12
  5              1             2018               6                         5                        11
  6              2             2018              15                        10                      25
;
proc tabulate data=have;
class region year ;
var male female;
table region=' ',year=' '*(male female)/box='REGION' ;
keylabel sum=' ';
run;

Re: Analyzing aggregate data using proc SQL

MaxRoy — Fri, 12 Jul 2019 12:54:01 GMT

It works.

Thank you very much Ksharp!