Return a unique integer value for each distinct evaluated value

[Betty_sam]

Hi,

Let me start off by saying that I'm very new to SAS so excuse me for any minor mistakes.

I have data like this:

Region	Year	Month	Sales
North	2014	May	245
North	2014	May	347
North	2014	June	127
North	2014	May	645
South	2013	May	367

 and I need to define a unique interger value for each distinct evaluated value of (region&year&month).

Data want :

Region	Year	Month	Sales	RYMkey
North	2014	May	245	1
North	2014	May	347	1
North	2014	June	127	2
North	2014	May	645	1
South	2013	May	367	3

How can I do this please ? 

Thank you for your help.

Betty

[PeterClemmensen · Accepted solution]

If you want to maintain the original order in the data, you can use a hash object like this

data have;
input Region $ Year Month $ Sales;
datalines;
North 2014 May  245 
North 2014 May  347 
North 2014 June 127 
North 2014 May  645 
South 2013 May  367 
;

data want(drop = c);
   if _N_ = 1 then do;
      dcl hash h();
      h.definekey('Region', 'Year', 'Month');
      h.definedata('RYMkey');
      h.definedone();
   end;
 
   set have;

   if h.find() ne 0 then do;
      c + 1;
      RYMkey = c;
   end;

   h.replace();

run;

Result:

Region  Year  Month  Sales  RYMkey 
North   2014  May    245    1 
North   2014  May    347    1 
North   2014  June   127    2 
North   2014  May    645    1 
South   2013  May    367    3 

[Betty_sam]

Peter,

Perfect ! 

Thank you so much 

Betty

[PeterClemmensen]

Anytime 🙂

[RichardDeVen]

Why did June change to May in 4th row ?

[Betty_sam]

It's just a typographical error