BookmarkSubscribeRSS Feed
cody_q
Calcite | Level 5

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

1 REPLY 1
Reeza
Super User

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.

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
  • 1 reply
  • 908 views
  • 0 likes
  • 2 in conversation