@ChrisNZ wrote: proc sql; select DATE , sum(VALUE*(CAT1=1))/sum(CAT1=1) as RATE1 ... from HAVE group by DATE; Oh!  Now, that's danged clever.  And I suppose that if the CATn variables had possible values greater than 1, then we could do the following:   proc sql; select DATE , sum(VALUE*(CAT1>0))/sum(IFN(CAT1>0,CAT1,0)) as RATE1 FORMAT=PERCENT10.2 , sum(VALUE*(CAT2>0))/sum(IFN(CAT2>0,CAT2,0)) as RATE2 FORMAT=PERCENT10.2 , sum(VALUE*(CAT3>0))/sum(IFN(CAT3>0,CAT3,0)) as RATE3 FORMAT=PERCENT10.2 , sum(VALUE*(CAT4>0))/sum(IFN(CAT4>0,CAT4,0)) as RATE4 FORMAT=PERCENT10.2 , sum(VALUE*(CAT5>0))/sum(IFN(CAT5>0,CAT5,0)) as RATE5 FORMAT=PERCENT10.2 , sum(VALUE*(CAT6>0))/sum(IFN(CAT6>0,CAT6,0)) as RATE6 FORMAT=PERCENT10.2 , sum(VALUE*(CAT7>0))/sum(IFN(CAT7>0,CAT7,0)) as RATE7 FORMAT=PERCENT10.2 from HAVE group by DATE;     Jim ... View more

@PaigeMiller,   For the Rate:  If the numerator is determined by the Value column and the denominator is determined by the Cat column, would a Proc Report give us the result we would want in this case?  An excellent technique in general though; one that didn't even occur to me.   Jim   ... View more

It would be helpful if you post some data -- even if it's just mocked up data.     One question I have is:  Can "Toyota" or "Honda" appear within other text or is the brand name of the car in a column by itself? For example, would your data look more like the "Brand" column below or would it look more like the "Car_Info" column below? Brand Car_Info Toyota 2014 Toyota Camry   If "Toyota" can appear anywhere in the column, then we need to take a different approach.    Jim ... View more

You're welcome.  I'm glad this helped!!   👍   Jim ... View more

OK, so I have to confess some (significant) ignorance here.  Does Mgmt Console underneath the covers format a SAS batch command?   For example, when I do an RSUBMIT, I first build a command string and then set the SASCMD option to the value of the command string. %LET Cmd_String = !sascmdv -logparm 'write=immediate rollover=session' -memsize 8G -altlog &LogPath\sub_process_logs\&Pgm._%Get_Date(FORMAT=YYMMDDD10)_%Get_Time(FORMAT=Dot_Time8).log; OPTION SASCMD = "&Cmd_String"; When I finally do the actual RSUBMIT, the string I've built is used to launch the new SAS batch job.  If you can create such a command string, environment variables can be created via the SET statement.  For example, the following, when included as part of the command string, sets an environment variable called "DSN" and assigns it a value of "AB_Potential_Charts_2020." -set DSN AB_Potential_Charts_2020 One could just as easily include a macro value in the command string for, say, a run date or an as of date or the like.   Then, inside the SAS program that is launched, I do a SYSGET to obtain the value of the environment variable. %LET &Var = %SYSGET(&Var); I basically have a macro whose argument is the name of the environment variable.  Using SYSGET, I create a macro variable with the same name as the environment variable and use the macro variable inside the program.     Perhaps that could be helpful; just thought I'd mention it.  I've found it particularly handy.   Jim   ... View more

I think I have slightly different data based on the original posting -- or I'm misunderstanding (which is entirely possible). Based on the data I have (see posted below), I get a 5 for the denominator for January, which is the count of "1's" in the Cat1 column.  For the numerator, I get a 3, which is the count of "1's" in the Value column with a corresponding "1" in the Cat1 column.  So, 3/5 = 0.6 or 60%, yes?  Full results, below, followed by revised code.   January Data:   Jan-18-2021 1 0 0 0 1 1 0 0 Jan-18-2021 0 1 1 0 0 1 1 0 Jan-18-2021 0 1 0 1 0 1 1 1 Jan-18-2021 1 1 0 0 1 0 0 Jan-18-2021 1 0 0 0 0 0 0 1 Jan-18-2021 0 1 0 1 0 0 1 0 Jan-18-2021 0 1 0 1 1 0 1 1 Jan-18-2021 0 0 1 0 0 1 1 1 Jan-18-2021 1 1 1 0 0 0 1 1 Jan-18-2021 1 0 0 0 1 1 0 1 Jan-18-2021 0 0 0 1 0 1 1 1   Results (all months):   Code: DATA Have; FORMAT Date YYMMD7.; INFILE DATALINES DSD DLM='09'X MISSOVER; INPUT Date : ANYDTDTE11. Cat1 Cat2 Cat3 Cat4 Cat5 Cat6 Cat7 Value; DATALINES; Jan-18-2021 1 0 0 0 1 1 0 0 Jan-18-2021 0 1 1 0 0 1 1 0 Jan-18-2021 0 1 0 1 0 1 1 1 Jan-18-2021 1 1 0 0 1 0 0 Jan-18-2021 1 0 0 0 0 0 0 1 Jan-18-2021 0 1 0 1 0 0 1 0 Jan-18-2021 0 1 0 1 1 0 1 1 Jan-18-2021 0 0 1 0 0 1 1 1 Jan-18-2021 1 1 1 0 0 0 1 1 Jan-18-2021 1 0 0 0 1 1 0 1 Jan-18-2021 0 0 0 1 0 1 1 1 Feb-18-2021 1 1 1 0 1 0 1 0 Feb-18-2021 0 1 0 1 0 0 0 0 Feb-18-2021 1 0 1 0 0 1 0 1 Feb-18-2021 0 0 1 0 1 1 0 1 Feb-18-2021 1 1 0 0 1 0 1 1 Feb-18-2021 1 0 0 1 0 0 1 1 Feb-18-2021 0 1 1 1 0 1 0 0 Feb-18-2021 1 1 0 1 1 1 1 0 Mar-18-2021 0 1 0 1 1 0 0 0 Mar-18-2021 1 1 0 1 0 1 0 0 Mar-18-2021 1 1 1 0 0 1 0 1 Mar-18-2021 0 1 0 0 0 1 1 0 Mar-18-2021 1 1 1 0 1 1 0 1 Mar-18-2021 1 1 1 1 1 0 1 1 Mar-18-2021 1 0 0 1 0 1 0 0 Mar-18-2021 1 1 0 0 0 0 0 0 Mar-18-2021 1 1 1 1 1 1 0 1 Mar-18-2021 0 1 0 1 1 1 1 1 Mar-18-2021 0 1 1 0 0 0 0 0 Mar-18-2021 0 1 1 1 0 0 0 0 Mar-18-2021 0 1 1 1 1 1 1 0 Mar-18-2021 0 0 1 1 1 1 1 0 Mar-18-2021 1 0 0 1 0 1 1 1 ; RUN; DATA Want; DROP _: Cat1 - Cat7 Value; SET Have; BY Date NOTSORTED; ARRAY Cats {*} Cat1 - Cat7; ARRAY Sums {7} _TEMPORARY_; ARRAY Cat_Cnt {7} _TEMPORARY_; ARRAY Rates {7} Rate1 - Rate7; FORMAT Rate1 - Rate7 PERCENT10.2; DO _i = 1 TO DIM(Cats); IF Cats{_i} THEN DO; Sums{_i} + Value; Cat_Cnt{_i} + 1; END; END; IF Last.Date THEN DO; DO _i = 1 TO DIM(Cats); Rates{_i} = Sums{_i} / Cat_Cnt{_i}; CALL MISSING(Sums{_i}, Cat_Cnt{_i}); END; OUTPUT; END; ELSE DO; DELETE; END; RUN; Jim ... View more

@sasprogramming wrote:   CA rate for cat1 Jan = sum of value = 3 divided by sum of cat = 4, therefore rate = 3/4 OK, well, then what I posted earlier is a bit off and needs adjustment.  Let me tweak it a bit.   Jim ... View more