Help using Base SAS procedures

Proc Sql Summary

Reply
Contributor
Posts: 27

Proc Sql Summary

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.
Super User
Posts: 5,257

Re: Proc Sql Summary

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
Data never sleeps
Contributor
Posts: 24

Re: Proc Sql Summary

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 flagSmiley Happy;
do i=1 To &numc;
company(i)=sums(i);
end;
output;
end;
run;
PROC Star
Posts: 1,562

Re: Proc Sql Summary

Would this help?

[pre]

proc summary data=MYDATA nway;
class ID;
var FIRM_:;
output out=TOTAL_SALES(drop=_Smiley Happy 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.
  • Ask a Question
    Discussion stats
    • 3 replies
    • 712 views
    • 0 likes
    • 4 in conversation