SAS Freq cross tabulation

[Right]

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!

[art297]

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.

[Right]

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?

[art297 · Accepted solution]

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;

[Right]

Hi art297,

Thank you for the quick reply! It works.

[art297]

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;

[Haikuo]

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

[Right]

Hi Haikuo,

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

Thanks!

[art297]

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

[Right]

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

[Linlin]

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

[Right]

Hi Linlin,

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