Analyzing aggregate data using proc SQL

[MaxRoy]

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!

[PeterClemmensen]

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;

[aaronh]

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.

[Ksharp]

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;

[MaxRoy]

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.

[Ksharp]

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;

[MaxRoy]

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

[aaronh]

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;

[Ksharp · Accepted solution]

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;

[MaxRoy]

It works.

Thank you very much Ksharp!