Counting ovservations with proc transpose

Question

In SAS, I have a table like this:

ID   ACTE 
1    A   
2    B  
1    A  
1    A  
2    C  
1    B  

and I would like to have the following table:

ID    A    B    C
 1    3    1    0
 2    0    1    1

The second table is a table with an identifier and for each value possible in the ACTE column, the amount of time that it has occurred for this identifier.

I would like to know if this can be done using proc transpose.

I tried this code, but it caused errors:

proc transpose data=OriginalTable out=TestTranspose;
    by ID;
    var Acte;
    id Acte;
 run;

Answer 1

You can use an intermediate step that calculates the counters and passes them to proc transpose. Example:

PROC SQL;
CREATE VIEW OriginalTable_v as 
SELECT
ID
,ACTE 
,COUNT(*) AS FREQ
FROM OriginalTable
GROUP BY ID ,ACTE 
ORDER BY ID ,ACTE ;
QUIT;

proc transpose data=OriginalTable_v out=TestTranspose;
    by ID;
    var FREQ;
    id Acte;
 run;

. , ACTE. , , , , 0.

- ACTE , . :

PROC SQL;
CREATE table TestTranspose2 as 
SELECT
ID
,sum(case when acte='A' then 1 else 0 end) as count_A
,sum(case when acte='B' then 1 else 0 end) as count_B
,sum(case when acte='C' then 1 else 0 end) as count_C
,sum(case when acte NOT IN ('A', 'B', 'C') then 1 else 0 end) as count_Other
,COUNT(*) AS FREQ_of_ids
FROM OriginalTable
GROUP BY ID
ORDER BY ID ;
QUIT;

, 0s, ID ACTE.

Answer 2

@user102890. Proc Freq , . , id acte .

data have;
input ID   ACTE $;
datalines;
1    A 
2    B 
1    A 
1    A 
2    C 
1    B 
;
run;

proc freq data=have noprint;
table ID*ACTE / out=temp sparse;
run;

proc transpose data=temp out=want (drop=_:);
by ID;
id ACTE;
var COUNT;
run;