topic Vertical to horizontal data with summing in SAS Programming

Vertical to horizontal data with summing

Sathish_jammy — Tue, 16 Jul 2019 12:08:31 GMT

Dear Experts,

I want to visualize my data as a horizontal format with summing the year value.

For example :

data lkj;
input ID yearof;
cards;
132 2004
132 2004
132 2005
321 2010
321 2010
321 2015
run;

Required OP

ID	2004	2005	2010	2015
132	2	1	.	.
321	.	.	2	1

Kindly help me to transform in the given format.

Thanks in advance!

Re: Vertical to horizontal data with summing

PaigeMiller — Tue, 16 Jul 2019 12:17:40 GMT

proc report data=lkj;
    columns id yearof;
	define id/group;
	define yearof/across;
run;

By the way, this is counting, not summing.

Re: Vertical to horizontal data with summing

PeterClemmensen — Tue, 16 Jul 2019 12:18:00 GMT

proc sql;
    create table temp as
    select *,
           count(*) as count
    from lkj
    group by ID, yearof;
quit;

proc transpose data=temp out=want(drop=_NAME_) prefix=_;
    by ID;
    id yearof;
    var count;
run;

Re: Vertical to horizontal data with summing

Ksharp — Tue, 16 Jul 2019 12:22:27 GMT

It is best tool for PROC TABULATE.

data lkj;
input ID yearof;
cards;
132 2004
132 2004
132 2005
321 2010
321 2010
321 2015
;
proc tabulate data=lkj;
class id yearof;
table id,yearof*n=' '*f=best.;
run;

Re: Vertical to horizontal data with summing

Sathish_jammy — Tue, 16 Jul 2019 12:25:45 GMT

Thank you for your support! @PaigeMiller @Ksharp @PeterClemmensen

All your suggestion works well.