BookmarkSubscribeRSS Feed
DaRE
Calcite | Level 5

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.

 

Example

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

 

6 REPLIES 6
ed_sas_member
Meteorite | Level 14

Hi @DaRE 

 

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!

 

Best,

PaigeMiller
Diamond | Level 26

Yes, SQL does that.

 

 

Try using the ORDER BY clause in PROC SQL.

--
Paige Miller
novinosrin
Tourmaline | Level 20

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;

 

 

PaigeMiller
Diamond | Level 26

I did not suggest using MONOTONIC(). In fact, I always advise against using it, as it is undocumented and unsupported and could produce incorrect results.

--
Paige Miller
novinosrin
Tourmaline | Level 20

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.

PaigeMiller
Diamond | Level 26

You are right ... there's no obvious way to maintain the order of the records in SQL, some part of this must be done in a data step. I didn't look carefully enough.

--
Paige Miller

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 480 views
  • 1 like
  • 4 in conversation