topic Re: Multiple columns and loop and create ranks in SAS Programming

Multiple columns and loop and create ranks

Emma8 — Mon, 26 Oct 2020 15:46:04 GMT

I have the following data:

Name Day1 Day2 Day3 Day4

Kim 2 3 9 0

Anna 0 1 2 9

Joe 5 5 9 0

Bob 2 0 9 0

Enward 2 6 5 0

I would like to get the top 3 ranks for each day and summation of remaining data and formatted as a long data as below:

Name Day Counts

Joe Day1 5

Bob Day1 2

Edward Day1 2 (when tie, the take the first name--Edward is before Kim)

Others Day1 2

Edward Day2 6

Joe Day2 5

Kim Day2 3
Others Day2 1

Bob Day3 9

Joe Day3 9

Kim Day3 9

Others Day3 7

Anna Day4 9

Bob Day4 0

Joe Day4 0

Others Day4 0

Actual data have many columns. Thank you.

Re: Multiple columns and loop and create ranks

mklangley — Mon, 26 Oct 2020 16:45:40 GMT

@Emma8 Is this what you're looking for?

data have;  
    input name $ day1-day4;
    datalines;
Kim 2 3 9 0
Anna 0 1 2 9
Joe 5 5 9 0
Bob 2 0 9 0
Edward 2 6 5 0
    ;
run;

%macro loop(days=);
    %do i = 1 %to &days.;
        proc sql;
            create table summarize as
            select Name, "Day&i." as day, sum(day&i.) as total
            from have
            group by name, day
            order by calculated total desc, name
            ;
        quit;

        data top_3;
            set summarize end=eof;
            retain sum 0;
            if _n_ gt 3
                then do;
                    sum + total;
                    if eof then do;
                        name = 'Others';
                        count = sum;
                        output;
                    end;
                end;
            else do;
                count = total;
                output;
            end;
            drop total sum;
        run;

        proc append base=want
            data=top_3
            force;
        run;
    %end;
%mend;

%loop(days=4);

Note: Per your example, I am assuming your columns are named Day1, Day2, etc. The parameter of the macro call can be modified if you have more columns (e.g. 7, if you have Day1-Day7).

Result:

Re: Multiple columns and loop and create ranks

Emma8 — Mon, 26 Oct 2020 18:31:15 GMT

HI-the actual data has ay1 to day50, so I changed days=4 to 50, and the day.i repeat after 9th day. Could you fix that, please? Thank you

Re: Multiple columns and loop and create ranks

mklangley — Mon, 26 Oct 2020 18:41:31 GMT

It shouldn't be repeating day&i., but you may be seeing the day column's format only showing four characters (i.e. so "Day10" is displayed as "Day1").

Try adding the following format statement to the top_3 data step, as shown below:

data top_3;
    format day $8.;
    /* . . . */

Adding that works for me when I update the input data to have 10+ days.