How to assign value to a percentage of dataset

[sugarflux]

Hi guys

I'm actually working in SQL within SAS. Bit of a n00b i'm afraid. I need to assign a value to a percentage of a table.. for example

the first third of records myVal=1
the second third of records myVal=2
the third third of records myval=3

So just to clarify in a table of 30 records, i need records 1-10 myval = 1
11-20 myval = 2
21-30 myval = 3

Thanks

sugarflux

 

[hvass]

You could create an auto_increment field on the table so each record gets a unique number in a series, work out total number of rows then calculate records are in the first third etc..

What are you trying to do though? If you are wanting to assign records to a set or sample etc.. there are better ways to do this... split a file into random set of 3 for example

interval(rand(),.34,.67,999) as setnumber

 

[sugarflux]

Thanks Hvass

Each record has a date and i wish to assign a score (in this case 1 to 3). The most recent dates would be 1 and the oldest 3.

Previously the data has always contained a years worth of records and i have assigned a score 1-4 seperating by quarter
(CASE
WHEN mydate between date1 and date2 then 1
WHEN mydate between date3 and date4 then 2
WHEN mydate between date5 and date6 then 3
WHEN mydate between date7 and date8 then 4
END) as myVal
etc.

Now i need to adapt this process so instead of seperating by logical values i need to create each set of data the same size (I'm also just using 3 groups instead of 4). Assigning a key probably would work but i don't know how to get the number of rows/obs

I guess if i could get the number of rows i could do a similar thing to above...
(CASE
WHEN myRowNum between 1 and myTotalRows/3 then 1
WHEN mydate between myTotalRows/3 and (myTotalRows/3)*2 then 2
WHEN mydate between (myTotalRows/3)*2 and myTotalRows then 3
END) as myVal
Cheers

sugarflux

 

[hvass]

Yes I think that would be how I would do it find the number of rows in the table then calculate the segment

select @tot:=count(*) from mytable;
set @rank:=0;
select
	somecols,
	@rank:=@rank+1,
	ceiling(@rank/@tot*3) as segment
from mytable
order by somedates;