topic Merging Proc Freq with a file in Statistical Procedures

Merging Proc Freq with a file

Nickley — Tue, 13 Dec 2011 01:00:18 GMT

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

Merging Proc Freq with a file

art297 — Tue, 13 Dec 2011 01:10:53 GMT

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

Merging Proc Freq with a file

Nickley — Tue, 13 Dec 2011 01:14:14 GMT

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;

Merging Proc Freq with a file

Reeza — Tue, 13 Dec 2011 01:17:18 GMT

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;

Merging Proc Freq with a file

art297 — Tue, 13 Dec 2011 01:18:26 GMT

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

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.

Merging Proc Freq with a file

Tom — Tue, 13 Dec 2011 01:27:12 GMT

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;

Merging Proc Freq with a file

Nickley — Tue, 13 Dec 2011 01:33:26 GMT

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

Merging Proc Freq with a file

Tom — Tue, 13 Dec 2011 02:52:59 GMT

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;