grouped ascending sorting

[Billybob73]

Hi,

I'd like to add column3. How do I do that in SAS ?

No sorting on column 2 ! It needs to be in the exact same order as below.

Can this be done in an SQL statement too ? If so , how ?

Thanks for your help !!

Rgds

B

COLUMN1	COLUMN2	COLUMN3
A	ABC	1
A	ABC	1
A	DEF	2
A	DEF	2
A	DEF	2
A	DEF	2
A	GHI	3
A	GHI	3
A	GHI	3
A	GHI	3
A	ABC	4
A	JKL	5
A	JKL	5
A	JKL	5
A	JKL	5

[novinosrin]

data want;

set have;

by col1 col2;

if first.col1 then col3=1;

else if first.col2 then col3+1;

run;

[Billybob73]

Hi,

Unfortunately, this is not it. "ERROR: By variables are not properly sorted on data set WORK.HAVE"

[novinosrin]

 @Billybob73   Oh I assumed yours was sorted

Ok try this

proc sort data=have;
by col1 col2;
run;

data want;
set have;
by col1 col2;
if first.col1 then col3=1;
else if first.col2 then col3+1;
run;

if not 

data want;
set have;
by col1 col2 notsorted;
if first.col1 then col3=1;
else if first.col2 then col3+1;
run;

[Billybob73]

Many thanks for your help, but the thing is it shouldn't be sorted..........

[novinosrin · Accepted solution]

ok i think this is what you need

data have;
input COLUMN1 $	COLUMN2 $;*	COLUMN3;
cards;
A	ABC	1
A	ABC	1
A	DEF	2
A	DEF	2
A	DEF	2
A	DEF	2
A	GHI	3
A	GHI	3
A	GHI	3
A	GHI	3
A	ABC	4
A	JKL	5
A	JKL	5
A	JKL	5
A	JKL	5
;

data want;
set have;
by COLUMN1 COLUMN2 notsorted;
if first.column1 then col3=1;
else if first.column2 then col3+1;
run;

[Billybob73]

Hi,

I forgot to see your second code. Sorry for that.

I can see now that this works !!! Excellent. Thanks for this!

Rgds

B

[Reeza]

Then add the NOTSORTED option after your BY variables. It will function as grouped, not sorted but give you what you want.

BY <list of vairalbes> NOTSORTED;

[Billybob73]

1 last question. Would there be a way to do this in a proc sql ?

[novinosrin]

@Billybob73   The answer is it is not impossible but not straight forward or even clear in just one pass i.e in other words that will involve creation of seq and self join and logical grouping and summing within a grouping. 

In a nutshell, proc sql isn't quite the oracle sql/teradata sql or microsoft sql server kind where you can do partition by/rownum and so on. It's more of an extension of the SAS language so to speak. HTH

[ballardw]

You don't show what you want to happen when column1 changes.

This replicates your example:

data want;
   set have;
   by column1 notsorted column2;
   if first.column2 then column3 +1;
run;

If you want the count to restart for each value of column1 then

data want;
   set have;
   by column1 notsorted column2;
   if first.column1 then column3=.;
   if first.column2 then column3 +1;
run;

This assumes the data is sorted by column1. If this is NOT the case then you need to provide a more complete example of the starting data and the corresponding desired output.

Note the use of a data step to provide data. Please try to use such so we can test code.

the Column3+1 used this way creates an implied RETAIN statement for Column3.

[Billybob73]

Thanks very much ! 

This is what I needed.

I didn't know the notsorted statement

Rgds

B

[Reeza]

Full tutorial here:

https://stats.idre.ucla.edu/sas/faq/how-can-i-create-an-enumeration-variable-by-groups/

[Billybob73]

Thanks Reeza, I'll have a look to get to know this better.