BookmarkSubscribeRSS Feed
SAS09
Calcite | Level 5
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.
3 REPLIES 3
LinusH
Tourmaline | Level 20
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
SUN59338
Obsidian | Level 7
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
Tourmaline | Level 20
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.
  • sas-innovate-2024.png

    Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

    Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

     

    Register now!

    What is Bayesian Analysis?

    Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

    Find more tutorials on the SAS Users YouTube channel.

    Click image to register for webinarClick image to register for webinar

    Classroom Training Available!

    Select SAS Training centers are offering in-person courses. View upcoming courses for:

    View all other training opportunities.

    Discussion stats
    • 3 replies
    • 2001 views
    • 0 likes
    • 4 in conversation