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,
RS
Perfect. Again thanks a lot for your quick response. very helpful 🙂
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;
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 |
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;
Perfect. Again thanks a lot for your quick response. very helpful 🙂
Would have been nice to mark the actual answer to the question as correct, rather than your response to that
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.