BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Nickley
Calcite | Level 5

I ran a PROC FREQ by state. The original file contained several rows for state, and the PROC FREQ summed the states into one line. How do I take the PROC FREQ and merge it with an actual file?

Thank you for the help!

Nicole

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

But which of the 67 rows do you want? 

Perhaps you want to summarize the 67 rows?

You might consider PROC SUMMARY (also known as PROC MEANS).

For example to get the mean of all the numeric variables (like the two mentioned above in your example code).

You can use a CLASS statement even if the data is not sorted by that variable.

proc summary data=walmart7 nway ;

   class state ;

   var _numeric_ ;

   output out=means mean= ;

run;

View solution in original post

7 REPLIES 7
art297
Opal | Level 21

It will be easier to answer your question if you provide some sample data and your proc freq code.

Nickley
Calcite | Level 5

data walmart7;

set walmartfin1;

where newin1990 = 1 ;

if dist gt 400 and dist lt 800 then output;

run;

proc sort data = walmart7;

by state;

run;

proc freq data = walmart7;

output;

run;

art297
Opal | Level 21

????????????

That proc freq won't produce anything.  And, by data, I meant a datastep that produces a small subset of what your data really look like.

Tom
Super User Tom
Super User

What about the PROC FREQ output do you want to merge back onto the original data?

Do you want the count of the number of records per state?

proc freq data=walmart7 ;

  tables state / out=counts ;

run;

data want ;

  merge counts walmart7 ;

  by state ;

run;

Nickley
Calcite | Level 5

Tom,

This is very helpful, thank you for your help! I have one additional question if you have the time. In the table, the state of Alabama shows up in 67 rows. Do you know how I would go about getting one row per state?

Thanks again for your help,

Nicole

Tom
Super User Tom
Super User

But which of the 67 rows do you want? 

Perhaps you want to summarize the 67 rows?

You might consider PROC SUMMARY (also known as PROC MEANS).

For example to get the mean of all the numeric variables (like the two mentioned above in your example code).

You can use a CLASS statement even if the data is not sorted by that variable.

proc summary data=walmart7 nway ;

   class state ;

   var _numeric_ ;

   output out=means mean= ;

run;

Reeza
Super User

Using SQL to get the counts in is a bit easier.

Replace have with your table name and it should work.

Proc SQL;

create table want as

select *, count(state) as num_obs

from have

group by state;

quit;

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!

What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 2292 views
  • 9 likes
  • 4 in conversation