topic Re: How to count the values of a column based on the value of another in New SAS User

How to count the values of a column based on the value of another

Abelp9 — Thu, 02 Jun 2022 12:44:58 GMT

Hello everyone, I am new to programming in SAS and I have a data sheet like the one in the example, in which column A has descriptions and the last column has the concatenation of all its fields, what I want to see is if that description has concatenations different since it should only have 1 assigned. I had thought about doing a rank by the concatenation field but I don't remember how it was done, could someone give me an example if its possible?

I hope that if there is an equal description, such as 'GUM', that I put a 1 in the first line and a 2 in the second, so then I can keep those greater than 2, which will be the ones with repeated concatenations, I don't know if I am explaining myself well, I hope so.

Thank you very much in advance, if you help me with this you would do me a great favor

Re: How to count the values of a column based on the value of another

PaigeMiller — Thu, 02 Jun 2022 12:57:05 GMT

Many of us will not download an Excel spreadsheet (or any MS Office document) as they can be security threats. Please provide (a portion of) your data as working SAS data step code, which you can type in yourself, or you can follow these instructions.

Re: How to count the values of a column based on the value of another

FreelanceReinh — Thu, 02 Jun 2022 14:02:11 GMT

Hello @Abelp9,

As far as I see from the preview of your input data, you can use PROC SQL:

proc sql;
create table want as
select a, /* concat, */ count(distinct concat) as cnt
from have
group by a
/* having cnt>1 */
;
quit;

You may want to use either or both of the parts that I've commented out to include the CONCAT values in the output dataset or to restrict it to descriptions with at least two different CONCAT values. Note that only non-missing CONCAT values will be counted (without further additions to the code).

Re: How to count the values of a column based on the value of another

Reeza — Thu, 02 Jun 2022 15:12:57 GMT

https://gist.github.com/statgeek/d583cfa992bf56da51d435165b07e96a

*create sample data for demonstration;
data have;
    infile cards dlm='09'x;
    input OrgID Product $   States $;
    cards;
1   football    DC
1   football    VA
1   football    MD
2   football    CA
3   football    NV
3   football    CA
;
run;

*Sort - required for both options;
proc sort data=have;
    by orgID;
run;

**********************************************************************;
*Use RETAIN and BY group processing to combine the information;
**********************************************************************;
data want_option1;
    set have;
    by orgID;
    length combined $100.;
    retain combined;

    if first.orgID then
        combined=states;
    else
        combined=catx(', ', combined, states);

    if last.orgID then
        output;
run;

**********************************************************************;
*Transpose it to a wide format and then combine into a single field;
**********************************************************************;
proc transpose data=have out=wide prefix=state_;
    by orgID;
    var states;
run;

data want_option2;
    set wide;
    length combined $100.;
    combined=catx(', ', of state_:);
run;