Help using Base SAS procedures

SAS Freq cross tabulation

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

SAS Freq cross tabulation

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!


Accepted Solutions
Solution
‎02-09-2012 02:57 PM
PROC Star
Posts: 7,363

SAS Freq cross tabulation

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;

View solution in original post


All Replies
PROC Star
Posts: 7,363

SAS Freq cross tabulation

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.

Occasional Contributor
Posts: 9

Re: SAS Freq cross tabulation

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?

Solution
‎02-09-2012 02:57 PM
PROC Star
Posts: 7,363

SAS Freq cross tabulation

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;

Occasional Contributor
Posts: 9

SAS Freq cross tabulation

Hi art297,

Thank you for the quick reply! It works.

PROC Star
Posts: 7,363

SAS Freq cross tabulation

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;

Respected Advisor
Posts: 3,124

SAS Freq cross tabulation

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

Occasional Contributor
Posts: 9

SAS Freq cross tabulation

Hi Haikuo,

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

Thanks!

PROC Star
Posts: 7,363

SAS Freq cross tabulation

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

Occasional Contributor
Posts: 9

Re: SAS Freq cross tabulation

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

Super Contributor
Posts: 1,636

Re: SAS Freq cross tabulation

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

Occasional Contributor
Posts: 9

SAS Freq cross tabulation

Hi Linlin,

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 277 views
  • 6 likes
  • 4 in conversation