BookmarkSubscribeRSS Feed
John_D
Calcite | Level 5

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.

3 REPLIES 3
Amir
PROC Star

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.

John_D
Calcite | Level 5

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.

TomKari
Onyx | Level 15

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

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 2478 views
  • 0 likes
  • 3 in conversation