I have some data with 500 records. I need to sum the numbers of the duplicate record. I did it using PROC SQL, but it change the order of the records at output.
Can anyone suggest me how PROC SQL plays with records. Also if i want to keep the original order of the records, how can i do it.
Thanks in advance.
Var1 Var2 Var3
100 135 ase
123 143 ase
124 53 per
400 250 fge
100 300 fge
What i need is:
Var1 Var2 Var3
223 278 ase
124 53 per
500 550 fge
I suggest to create a temporary variable to assign the initial order:
data have; input Var1 Var2 Var3 $; datalines; 100 135 ase 123 143 ase 124 53 per 400 250 fge 100 300 fge ; run; data have2; set have; by Var3 notsorted; if first.Var3 then count+1; run; proc sql; create table want as select sum(Var1) as Var1, sum(Var2) as Var2, Var3 from have2 group by Var3, count order by count; quit; proc print;
Hope this helps!
Good morning Sir @PaigeMiller Very clever. Honestly,I didn't believe at first that the order by could be used with monotonic() to keep the original order considering group by does a presort and the likelihood of keeping the original order would be highly ambiguous for SQL processor to choose which observation number within a by group(being unique)
So For what's it's worth, I tested adding an A group as the last group on purpose. It seemed to work:)
data have; input Var1 Var2 Var3 $; datalines; 100 135 ase 123 143 ase 124 53 per 400 250 fge 100 300 fge 100 135 a 123 143 a ; run; proc sql; create table want as select sum(Var1) as Var1, sum(Var2) as Var2, Var3 from (select *,monotonic() as rn from have) group by var3 order by rn; quit;
Yes sorry. What I meant was the idea of ORDER BY clause. However, for ORDER BY clause to work here, we need a way to have the rowcount for each group as the OP wants the output in the original order. Of course this is easy should we resort to pre datastep, but assuming OP may want an all PROC SQL solution, I can't think of an alternative to monotonic() prior to order by.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.