Desktop productivity for business analysts and programmers

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

Posts: 24

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

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)

prt scr 1.jpg

Super User
Posts: 19,132

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

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 Smiley Happy

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.

Ask a Question
Discussion stats
  • 1 reply
  • 2 in conversation