topic SAS Freq cross tabulation in SAS Procedures

SAS Freq cross tabulation

Right — Thu, 09 Feb 2012 19:22:01 GMT

Hi All,

Does any of you know how to store the counts from SAS/Freq cross tabulation in a column and add this column to the original data?

I have several duplicated rows in the data, but these rows only duplicate in the first two columns. I need to save the counts in the cross tabulation for the first two columns and add it as a new column for the entire dataset.

Thanks!

SAS Freq cross tabulation

art297 — Thu, 09 Feb 2012 19:27:02 GMT

I think you will get the best answer, and quickest, if you provide some same have and want data that shows what you have and what you would like to have.

Re: SAS Freq cross tabulation

Right — Thu, 09 Feb 2012 19:50:52 GMT

Hi art297,

Thanks for the reminder.

Suppose this is the dataset.

ID Date Price Quantity

1 20001001 1.2 5.6

1 20001002 1.3 8.9

1 20001002 1.3 9.9

2 20101110 2.9 3.3

2 20101111 1.5 2.1

3 19990314 1.6 0.9

3 19990314 1.6 0.8

3 19990315 1.7 0.7

And below is the dataset I want to construct, where I count the duplicated rows (in the 1st two columns):

ID Date Price Quantity Frequency

1 20001001 1.2 5.6 1

1 20001002 1.3 8.9 2

1 20001002 1.3 9.9 2

2 20101110 2.9 3.3 1

2 20101111 1.5 2.1 1

3 19990314 1.6 0.9 2

3 19990314 1.6 0.8 2

3 19990315 1.7 0.7 1

Any suggestions?

SAS Freq cross tabulation

art297 — Thu, 09 Feb 2012 19:57:04 GMT

I would just do it with a datastep. e.g.,

data have;

input ID Date yymmdd8. Price Quantity;

cards;

1 20001001 1.2 5.6

1 20001002 1.3 8.9

1 20001002 1.3 9.9

2 20101110 2.9 3.3

2 20101111 1.5 2.1

3 19990314 1.6 0.9

3 19990314 1.6 0.8

3 19990315 1.7 0.7

;

data want;

do until (last.price);

set have;

by id date price;

if first.price then frequency=1;

else frequency+1;

end;

do until (last.price);

set have;

by id date price;

output;

end;

run;

SAS Freq cross tabulation

Haikuo — Thu, 09 Feb 2012 19:58:25 GMT

You probably can remerge your proc freq output to get what your want. Here is to show you 2xDWL will do the trick as well:

data have;

infile cards;

input ID $ Date :$8. Price Quantity;

cards;

1 20001001 1.2 5.6

1 20001002 1.3 8.9

1 20001002 1.3 9.9

2 20101110 2.9 3.3

2 20101111 1.5 2.1

3 19990314 1.6 0.9

3 19990314 1.6 0.8

3 19990315 1.7 0.7

;

data want;

do _n_=1 by 1 until(last.date);

set have;

by id date;

end;

do until(last.date);

set have;

by id date;

frequency=_n_;

output;

end;

run;

proc print;run;

Regards,

Haikuo

Re: SAS Freq cross tabulation

Linlin — Thu, 09 Feb 2012 19:59:19 GMT

by sql:

data have;

informat date yymmdd10.;

format date yymmdd10.;

input ID Date Price Quantity ;

cards;

1 20001001 1.2 5.6

1 20001002 1.3 8.9

1 20001002 1.3 9.9

2 20101110 2.9 3.3

2 20101111 1.5 2.1

3 19990314 1.6 0.9

3 19990314 1.6 0.8

3 19990315 1.7 0.7

;

proc sql;

create table want as

select *,count(*) as frequency

from have

group by id,date;

proc print;run;

Obs date ID Price Quantity frequency

1 2000-10-01 1 1.2 5.6 1

2 2000-10-02 1 1.3 9.9 2

3 2000-10-02 1 1.3 8.9 2

4 2010-11-10 2 2.9 3.3 1

5 2010-11-11 2 1.5 2.1 1

6 1999-03-14 3 1.6 0.8 2

7 1999-03-14 3 1.6 0.9 2

8 1999-03-15 3 1.7 0.7 1

Linlin

SAS Freq cross tabulation

Right — Thu, 09 Feb 2012 20:07:56 GMT

Hi art297,

Thank you for the quick reply! It works.

SAS Freq cross tabulation

art297 — Thu, 09 Feb 2012 20:09:30 GMT

p.s. and here is what you actually requested:

proc freq data=have noprint;

tables id*date/out=need

(drop=percent);

run;

data want;

merge have need;

by id date;

run;

SAS Freq cross tabulation

Right — Thu, 09 Feb 2012 20:16:21 GMT

Hi Haikuo,

By reading your and other's replies, I think I've just learned how to do loops in SAS.

Thanks!

SAS Freq cross tabulation

Right — Thu, 09 Feb 2012 20:19:35 GMT

Hi Linlin,

Thanks again for taking time writing me the neat sql code!

SAS Freq cross tabulation

art297 — Thu, 09 Feb 2012 20:21:34 GMT

FYI: Actually, in this thread, you learned how to use a DOW loop. You can find a nice explanation at: http://support.sas.com/resources/papers/proceedings09/038-2009.pdf

Re: SAS Freq cross tabulation

Right — Fri, 10 Feb 2012 16:14:46 GMT

This is helpful! Looks like do-loop can also do the job of a DOW-loop, but with more programming and accumulation variables.