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.
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.
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.
proc sort data = yourdata;
select name into: companys separated by ' '
where libname eq "WORK" AND MEMNAME eq "YOURDATA"
and name like "Firm_%";
%let numc = &sqlobs.;
array company(&numc) &companys.;
retain flag: sums:;
drop i flag: sums:;
if first.saleID then do i=1 to &numc.;
do i=1 to &numc;
if not missing(company(i)) then flag(i)=1;
if last.saleID then do;
do i=1 To &numc;