Help using Base SAS procedures

SAS Proc means

Accepted Solution Solved
Reply
Contributor
Posts: 63
Accepted Solution

SAS Proc means

 

I want to summarize data in the following way. I explored Proc Means but couldn't figured out an option to do this. 

 

Data have,

Company                Year                                  Country of operations

A                             2000                                  GBR

A                             2000                                  GBR

A                             2000                                  USA

A                             2001                                  FRA

A                             2002                                  USA

A                             2002                                  KSA

A                             2002                                  RUS

A                             2002                                  KSA

A                             2003 

B                             2000

 

Data Want

 

Company              Year                          Number of Countries represented

A                            2000                         2

A                            2001                         1

A                            2002                         3  

A                            2003

B                            2000

 

Thanks,


Accepted Solutions
Solution
‎11-25-2015 11:52 AM
Super Contributor
Posts: 340

Re: SAS Proc means

Posted in reply to buckeyefisher

If you insist on proc means you would need some numeric dummy variable:

 

Data Have;
 Input Company $ Year Country_of_operations $;
 If not Missing (Country_of_operations) Then Flag=1;
 Datalines;
A 2000 GBR
A 2000 GBR
A 2000 USA
A 2001 FRA
A 2002 USA
A 2002 KSA
A 2002 RUS
A 2002 KSA
A 2003 .
B 2000 .
;

Proc Sort Data=Have Nodupkey;
  By Company Year Country_of_operations;
Run;

Proc Means Data=Have NoPrint;
  Label Flag='Number of ..';
  By Company Year;
  Var Flag;
  Output Out=Want (Drop=_: ) Sum=;
Run;

View solution in original post


All Replies
Super User
Posts: 19,872

Re: SAS Proc means

[ Edited ]
Posted in reply to buckeyefisher

None of the standard procs do a distinct count as far as I know, two ways of accomplishin this is two proc freqs or a proc sql. 

proc freq data=have;
table company*year*country/out=p1;
run;

proc freq data=p1;
table company*year/out=want1;
run;

proc sql;
create table want2 as
select company, year, count(distinct country) as count
from have
group by company, year
order by company, year;
quit;
Valued Guide
Posts: 860

Re: SAS Proc means

Posted in reply to buckeyefisher

I think this is what you want but your output doesn't match.  If this is wrong explain how you came up with the numbers:

 

proc sql;
create table want as
select company,year,count(country) as number_of_countries
from have
group by company,year;

Contributor
Posts: 63

Re: SAS Proc means

Posted in reply to Steelers_In_DC

Solution
‎11-25-2015 11:52 AM
Super Contributor
Posts: 340

Re: SAS Proc means

Posted in reply to buckeyefisher

If you insist on proc means you would need some numeric dummy variable:

 

Data Have;
 Input Company $ Year Country_of_operations $;
 If not Missing (Country_of_operations) Then Flag=1;
 Datalines;
A 2000 GBR
A 2000 GBR
A 2000 USA
A 2001 FRA
A 2002 USA
A 2002 KSA
A 2002 RUS
A 2002 KSA
A 2003 .
B 2000 .
;

Proc Sort Data=Have Nodupkey;
  By Company Year Country_of_operations;
Run;

Proc Means Data=Have NoPrint;
  Label Flag='Number of ..';
  By Company Year;
  Var Flag;
  Output Out=Want (Drop=_: ) Sum=;
Run;
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 441 views
  • 0 likes
  • 4 in conversation