🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Obsidian | Level 7

Cross tabulation - Frequency Table

Hi all,

The dataset is:

FY          TC

2013        1
2014        5
2013        6
2015        7
2016        1
2015        5
2016        2
2014        2
2013        7
2014        4
2017        5
2018        1
2018        6
2015        4
2014        2
2015        4

The important point in the above output is that there is no data point for TC=3 but I want it in my output dataset, which I need later for calculation in another step. Again this TC=3 data unavailability is just for depiction only and for one particular category (eg. commercial real estate). For other categories, I might have data points missing for TC=4 (e..g for residential real estate). So I need a cross table where I can have frequency columns for each from TC=1 to TC=7 irrespective of the fact whether any datapoint is available for TC=1 to TC=7 or not.

I am well aware of PROC REPORT but it is not creating tables for TC=3. I think it can be done using PROC SQL. Please help me here. I prefer PROC SQL, PROC REPORT as their output can be used easily in a later step.

Not preferred: PROC TABULATE, PROC FREQ

1 ACCEPTED SOLUTION

Accepted Solutions
Rhodochrosite | Level 12

Re: Cross tabulation - Frequency Table

This can be easily done in SQL with sum.

``````data sample;
input FY TC;
datalines;
2013 1
2014 5
2013 6
2015 7
2016 1
2015 5
2016 2
2014 2
2013 7
2014 4
2017 5
2018 1
2018 6
2015 4
2014 2
2015 4
;
proc print;
run;

proc sql;
select FY,
sum(TC=1) as tc1,
sum(TC=2) as tc2,
sum(TC=3) as tc3,
sum(TC=4) as tc4,
sum(TC=5) as tc5,
sum(TC=6) as tc6,
sum(TC=7) as tc7
from sample
group by FY;
quit;``````
7 REPLIES 7
Rhodochrosite | Level 12

Re: Cross tabulation - Frequency Table

Can you please post the data as data lines?
Rhodochrosite | Level 12

Re: Cross tabulation - Frequency Table

This can be easily done in SQL with sum.

``````data sample;
input FY TC;
datalines;
2013 1
2014 5
2013 6
2015 7
2016 1
2015 5
2016 2
2014 2
2013 7
2014 4
2017 5
2018 1
2018 6
2015 4
2014 2
2015 4
;
proc print;
run;

proc sql;
select FY,
sum(TC=1) as tc1,
sum(TC=2) as tc2,
sum(TC=3) as tc3,
sum(TC=4) as tc4,
sum(TC=5) as tc5,
sum(TC=6) as tc6,
sum(TC=7) as tc7
from sample
group by FY;
quit;``````
Obsidian | Level 7

Re: Cross tabulation - Frequency Table

Thank you very much !!
It is working.
Rhodochrosite | Level 12

Re: Cross tabulation - Frequency Table

``````proc format;
value yrfmt
2013=2013
2014=2014
2015=2015
2016=2016
2017=2017
;

value tcfmt
1=1
2=2
3=3
4=4
5=5
6=6
7=7
;
run;

proc tabulate data=sample out=counts;
class FY tc / preloadfmt;
format tc tcfmt. fy yrfmt.;
table FY*tc / printmiss;
run;

proc sort data=counts;
by fy;
run;

proc transpose data=counts out=counts_t (drop=_name_) prefix=tc;
by fy;
var N;
id tc;
run;``````

You can also do with proc tabulate despite you saying it's against your preference. Preloadfmt allows you to get all possible combinations of TC and FY so that you can get the 0s for TC3. You can output the data from proc tabulate and then sort and transpose it.

Obsidian | Level 7

Re: Cross tabulation - Frequency Table

It is also working. Thanks again.
PROC SQL is a lot easier to understand and code. I prefer simplicity.
Diamond | Level 26

Re: Cross tabulation - Frequency Table

A PROC REPORT solution:

``````data sample;
input FY TC;
datalines;
2013 1
2014 5
2013 6
2015 7
2016 1
2015 5
2016 2
2014 2
2013 7
2014 4
2017 5
2018 1
2018 6
2015 4
2014 2
2015 4
;
data intermediate;
set sample end=eof;
weight=1;
output;
if eof then do;
tc=3;
weight=0;
output;
end;
run;
options missing=0;
proc report data=intermediate;
columns fy tc,weight;
define fy/group;
define tc/across;
define weight/sum ' ';
rbreak after/summarize;
run;``````

I prefer PROC SQL, PROC REPORT as their output can be used easily in a later step. Not preferred: PROC TABULATE, PROC FREQ

All output from any SAS PROC can be used in a later step.

--
Paige Miller
Obsidian | Level 7

Re: Cross tabulation - Frequency Table

Thanks a lot !!
It is working on SAS OnDemand but surprisingly not giving the desired output (tc=3 missing) in SAS Enterprise 7.15.

But got the idea about the logic you are using.
Discussion stats
• 7 replies
• 1428 views
• 6 likes
• 3 in conversation