How do i count a value from an excel file, using SAS EG?

cody_q — Wed, 20 Mar 2013 15:43:24 GMT

Dear all,

I have a file an excel file of 1048575 rows .

This is an example of the three lines.

Column A is the user_id

Column B-T represents values which may occur repeatedly through the file.

Column V1 to V3 is the count of the values from (B-T)

I have tried using the COUNTIF method on Excel but required me to do it manually lines by lines.
Hence, would like to know if SAS EG could support that large a file and if there is any SAS Code which allows me to count the frequency of the value automatically.

Thank you.

(Click on the pic to see it clearly)

Re: How do i count a value from an excel file, using SAS EG?

Reeza — Wed, 20 Mar 2013 16:32:30 GMT

Two options:

The cell references don't change so you should be able to automate your countif in Excel. It'll be slow because you have a big table, but easily done.

Fix your range in the first one using the $ signs or highlighting the range in the formula and pressing F4. The second reference is the list you're looking for, assuming you know it

Countif($A$1:$Z$256, ZZ24);

Otherwise, import your data into SAS - that many rows won't be an issue, but you'll want to verify that all the . and numbers come in as the appropriate type.

Transpose or restructure your data so that each record is an individual-response rather than multiple response per row.

Run a proc freq summary or summarize task on the data.

The hardest part will be the import, in my opinion.

topic How do i count a value from an excel file, using SAS EG? in SAS Enterprise Guide

How do i count a value from an excel file, using SAS EG?

Re: How do i count a value from an excel file, using SAS EG?