Cross tabulation - Frequency Table in SAS

0
votes

The dataset is:

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
;

Output desired:

    FY  tc1 tc2 tc3 tc4 tc5 tc6 tc7
2013    1   0   0   0   0   1   1
2014    0   2   0   1   1   0   0
2015    0   0   0   2   1   0   1
2016    1   1   0   0   0   0   0
2017    0   0   0   0   1   0   0
2018    1   0   0   0   0   1   0

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) or so on. 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 data point 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

3
sqlsql-serversassas-macroproc
As per the question guide, please show what you’ve tried and tell us what you found (on this site or elsewhere) and why it didn’t meet your needs. - Dale K
Weird restriction when proc tabulate and freq both deal with this quite well, using preloadfmt or the sparse option. - Reeza
@Reeza they don't take use those values that don't exist in the dataset. In my dataset no Fy has tc=3 but i need it in my output - Vaibhav Kabdwal
@DaleK I have removed the images - Vaibhav Kabdwal
@VaibhavKabdwal yes they very much can with the SPARSE or the PRELOADFMT options. - Reeza

3 Answers

1
votes

You can use a format and the preloadfmt with completerows or completecols to get what you're looking for. Below is the simple version of this, you probably need completecols with an across variable for yours - can provide that if you provide actual code to produce your table.

proc format;
 value age
 9="Nine"
 10="Ten"
 11="Eleven"
 12="Twelve"
 13="Thirteen"
 14="Fourteen"
 15="Fifteen"
 16="Sixteen"
 17="Seventeen"
 ;;;;
run;
 
proc report data=sashelp.class completerows;
column age age=count;
format age age.;
define age/preloadfmt group order=internal;
define count/computed n;
run;
0
votes

I posted this question on the SAS community as well. I am copying some top answers from there.

Getting desired using PROC REPORT:

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;

Credits: Paige Miller (SAS Community)

Getting desired using PROC TABULATE:

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;

Credits: Irackley (SAS Community)

0
votes

The top answer from SAS Community:

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;

Credits: Irackley (SAS Community)