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!
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;
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.
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?
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;
Hi art297,
Thank you for the quick reply! It works.
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;
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
Hi Haikuo,
By reading your and other's replies, I think I've just learned how to do loops in SAS.
Thanks!
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
This is helpful! Looks like do-loop can also do the job of a DOW-loop, but with more programming and accumulation variables.
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
Hi Linlin,
Thanks again for taking time writing me the neat sql code!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.