topic Re: Transpose issue in SAS Programming

How to create a summary table with percentages and sums

RJY — Tue, 27 Nov 2018 09:06:53 GMT

Hi All,

Too look, it maybe simple. But i am stuck with this. Kindly help.

Input Data set:

Data country;
input country $ siteid;
cards;
IN 101
IN 101
IN 102
US 101
US 101
US 101
US 101
US 102
US 102
US 103
EU 105
EU 105
EU 105
EU 106
;

Required Output:

Country	Siteid	Total	Percent
EU	105	3	21.43
EU	106	1	7.14
EU	All	4	28.57
IN	101	2	14.29
IN	102	1	7.14
IN	All	3	21.43
US	101	4	28.57
US	102	2	14.29
US	103	1	7.14
US	All	7	50.00

Currently i am using below code. It is not giving right ouput.

data country1;
set country;
output;
siteid = "All";
output;
siteid = "All";
run;

proc sort data = country1;
by country;
run;

proc freq data = country;
tables country * siteid / out = country_1 ;
by country;
run;

Thanks,

Re: Transpose issue

RW9 — Tue, 27 Nov 2018 09:06:04 GMT

Not a transpose issue by the way.

Something like:

proc sql;
 create table want as
 select country,
        siteid,
        count(*) as total,
        (count(*)/(select count(*) from country))*100 as percent
 from   country
 group by country,
          siteid;
quit;

Re: Transpose issue

RJY — Tue, 27 Nov 2018 09:14:21 GMT

Thanks a lot for your quick response. With your code 'All' value is not populating. Temporarily, I am also using proc sql steps for each country separately and appending to one table. But this will be length looking for simple program.

Country	Siteid	Total	Percent
EU	105	3	21.43
EU	106	1	7.14
EU	All	4	28.57
IN	101	2	14.29
IN	102	1	7.14
IN	All	3	21.43
US	101	4	28.57
US	102	2	14.29
US	103	1	7.14
US	All	7	50.00

Re: Transpose issue

RW9 — Tue, 27 Nov 2018 09:25:48 GMT

proc sql;
 create table want as
 select * 
 from   (
          select country,
                 strip(put(siteid,best.)) as siteid,
                 1 as ord,
                 count(*) as total,
                 (count(*)/(select count(*) from country))*100 as percent
          from   country
          group by country,
                   siteid
          union all
          select country,
                 "All",
                 2 as ord,
                 count(*),
                 (count(*)/(select count(*) from country))*100
          from   country
          group by country)
  order by country,ord;
quit;

Re: Transpose issue

RJY — Tue, 27 Nov 2018 09:38:40 GMT

Perfect. Again thanks a lot for your quick response. very helpful 🙂

Re: Transpose issue

RW9 — Tue, 27 Nov 2018 11:06:45 GMT

Would have been nice to mark the actual answer to the question as correct, rather than your response to that

Re: How to create a summary table with percentages and sums

Ksharp — Tue, 27 Nov 2018 12:41:48 GMT


Data country;
input country $ siteid;
cards;
IN 101
IN 101
IN 102
US 101
US 101
US 101
US 101
US 102
US 102
US 103
EU 105
EU 105
EU 105
EU 106
;
proc tabulate data=country;
class country siteid;
table country=' '*(siteid all), n='Total' pctn='Percent'
/box='country';
run;