topic Re: Return a unique integer value for each distinct evaluated value in SAS Programming

Return a unique integer value for each distinct evaluated value

Betty_sam — Thu, 11 Mar 2021 13:27:08 GMT

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

Re: Return a unique integer value for each distinct evaluated value

PeterClemmensen — Thu, 11 Mar 2021 11:39:54 GMT

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

Re: Return a unique integer value for each distinct evaluated value

RichardDeVen — Thu, 11 Mar 2021 12:03:40 GMT

Why did June change to May in 4th row ?

Re: Return a unique integer value for each distinct evaluated value

Betty_sam — Thu, 11 Mar 2021 13:22:18 GMT

It's just a typographical error

Re: Return a unique integer value for each distinct evaluated value

Betty_sam — Thu, 11 Mar 2021 13:24:03 GMT

Peter,

Perfect !

Thank you so much

Betty

Re: Return a unique integer value for each distinct evaluated value

PeterClemmensen — Thu, 11 Mar 2021 13:36:03 GMT

Anytime 🙂