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
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
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
Peter,
Perfect !
Thank you so much
Betty
Anytime 🙂
It's just a typographical error
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.