Proc Sql Summary

[SAS09]

Hoping I can get some ideas on how to do a summary report in proc sql. Here's what I am trying to create:

I have a dataset with multiple records for every sales agent. In addition, I have for the columns a number of different company ID's - all the company ID's have a prefix of Firm_ followed by the company id - for example - Firm_XYZ3D - there could be as many as 1000 company ids. The company id columns contains the sales for these companies that are associated with each sales agent. I need to create a summary report that shows every distinct sales agent ID and the the number of non-zero company IDs he or she is associated with. In addition I also need to determine the total sales per each company ID for every distinct sales agent.

Using proc sql to sum/count each distinct company id for every sales agent is quite laborious.

Does anyone have any thoughts on how I might approach this. I would greatly appreciate any feedback.

[LinusH]

So do you have around 1000 columns in your table, if I understand you right?
To do this in SQL is quite hard. I suggest that transpose your data so that you'll have a company column with the different company id's, and a column for sales for each combination of sales rep and company id. You could use PROC TRANSPOSE or a data step to accomplish this.
Then you could use SQL to do the summary report.

/Linus

[SUN59338]

seems you have a variable to present each company, so you may like to define a array to include all companys and use a do loop over the array to check no missing sale value for the company.
ie.
proc sort data = yourdata;
by saleID;
run;
proc sql;
select name into: companys separated by ' '
from dictionary.columns
where libname eq "WORK" AND MEMNAME eq "YOURDATA"
and name like "Firm_%";
quit;
%let numc = &sqlobs.;
data new;
set yourdata;
by saleID;
array company(&numc) &companys.;
array flag(&numc);
array sums(&numc);
retain flag: sums:;
drop i flag: sums:;
if first.saleID then do i=1 to &numc.;
flag(i)=0;
sums(i)=0;
end;
do i=1 to &numc;
if not missing(company(i)) then flag(i)=1;
sums(i)=sum(sums(i),company(i));
end;
if last.saleID then do;
noe_zero=sum(of flag:);
do i=1 To &numc;
company(i)=sums(i);
end;
output;
end;
run;

[ChrisNZ]

Would this help?

[pre]

proc summary data=MYDATA nway;
class ID;
var FIRM_:;
output out=TOTAL_SALES(drop=_:) sum=;
run;

data NB_FIRMS(drop=J);
set TOTAL_SALES;
array X

FIRM_:;
do J=1 to dim(X);
NB_FIRMS=sum(NB_FIRMS, X > 0);
end;
*rather than the loop above, use this if you have missing values when there is no sales;
NB_FIRMS2=n(of X

);
run;
[/pre]
proc sql cannot use the colon in this manner unfortunately, and if you must use sql you'll have to create a macro variable containing the list of variables as the previous post shows.


Message was edited by: Chris@NewZealand

Simplified the reply.