Desktop productivity for business analysts and programmers

Counting number of non-numeric values within multiple columns on the same observation.

Reply
New Contributor
Posts: 2

Counting number of non-numeric values within multiple columns on the same observation.

Hello Smiley Happy.  I'm new to SAS EG.  I have an issue that intuitively feels that it should be really easy to resolve in SAS EG, but for the life of me I can't find the solution/function to make it work.  I'm sure I'll be embarrassed by the answer.

How can I calculate the columns "Count of A", "Count of B" and "Count of null"?  The N function only seems to work with number formats and the Count function only seems to work within one column - but I'm happy to be corrected.

Incidentally in real life there could be upwards of 30 columns to check.

CustomerIDProduct2011_012011_022011_032011_042011_052011_06Count of ACount of BCount of null
111Widgets AA B 213
111Gadgets AAAB312
222GadgetsAA 204
333WidgetsA AA303
333GadgetsAAAB B321
333Gizmos AAA 303

Thanks in advance for any tips.

Super Contributor
Posts: 282

Re: Counting number of non-numeric values within multiple columns on the same observation.

Hi,

Does the following help?:

data _null_;

  length var1-var6 $ 1;

  var1='';

  var2='A';

  var3='A';

  var4='';

  var5='B';

  var6='';

  count_of_a   =count(cat(of var1-var6),'A');

  count_of_b   =count(cat(of var1-var6),'B');

  count_of_null=count(cat(of var1-var6),'');

  put count_of_a=;

  put count_of_b=;

  put count_of_null=;

run;

Regards,

Amir.

New Contributor
Posts: 2

Re: Counting number of non-numeric values within multiple columns on the same observation.

Hi Amir,

I don't know!  I was hoping I could do this simply in a computed column, for example.  I don't have any experience of inputting code in SAS EG.

Trusted Advisor
Posts: 1,059

Re: Counting number of non-numeric values within multiple columns on the same observation.

Hi, John

The easiest way to do this is a little roundabout; the advantage is that you won't need to worry about how many date columns you have.

Give this a try, and let us know what you think.

Step 1: Transpose the data

  • With your dataset open in EG, click on Data | Transpose...
  • Move your date columns to the "Transpose variables" role, and move CustomerID and Product to the "Group analysis by" role.

Run the task. You should see a result with just three columns, but the date columns have been converted to rows. Now any of the SAS analysis tools will work. Here's an example:

Step 2: Produce the table

  • With your transposed data open, click on Describe | Summary Tables
  • Move CustomerID, Product, and the transposed dates (probably "Column1") to the Classification variables role.
  • On the "Summary Tables" tab, move CustomerID and Product to the row dimension, and Column1 to the column dimension. Leave N as the statistic.

Run the task. You should see something like this:

Table.png

Ask a Question
Discussion stats
  • 3 replies
  • 962 views
  • 0 likes
  • 3 in conversation