BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
foxrol94
Fluorite | Level 6

Hi

I'm a bit upset about myself cause i can find the solution of my problem.

I have a dataset test1 with one variable Var1 and i need to create a second variable Var2with number 1, 2,3 etc.. except that each time i have two identical value in the first column, the value in the second column will be empty.

i tried the code below and nothing works:

 

data test2;
   set test1;
      by Var1;
           if first.Var1 then Var2 = _N_;
           else Var2 =.;
run;

Regards

 

First_second.PNG

 

1 ACCEPTED SOLUTION
3 REPLIES 3
Reeza
Super User

I suspect you have a note in your log about having unsorted data?

 

BY processing and FIRST only work if records are in order and in this case they're not in order. Does the order matter to the value assigned in VAR2 or do you just need to map then to a number?

 

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

 


@foxrol94 wrote:

Hi

I'm a bit upset about myself cause i can find the solution of my problem.

I have a dataset test1 with one variable Var1 and i need to create a second variable Var2with number 1, 2,3 etc.. except that each time i have two identical value in the first column, the value in the second column will be empty.

i tried the code below and nothing works:

 

data test2;
   set test1;
      by Var1;
           if first.Var1 then Var2 = _N_;
           else Var2 =.;
run;

Regards

 

First_second.PNG

 


 

 

FreelanceReinh
Jade | Level 19

Hi @foxrol94,

 

So, in order to set Var2 correctly, you need to have SAS remember all Var1 values it has already seen while reading through dataset TEST1? This is a typical application of the hash object.

data want(drop=_c);
if _n_=1 then do;
  dcl hash h();
  h.definekey('Var1');
  h.definedone();
end;
set test1;
if Var1 ne ' ' & h.check() then do;
  _c+1;
  Var2=_c;
  h.add();
end;
run;

If a new non-missing* Var1 value is encountered, the return code of the CHECK method is not zero and the DO-END block is executed: A counter _c, initialized to zero, is incremented (and automatically retained), Var2 (which otherwise would be missing) is set to the new value of _c and the value of Var1 is added to the hash object ("lookup table").

 

*Edit: inserted IF condition Var1 ne ' ' to exclude missing values from the count. Alternatively, you could insert an additional h.add(); after h.definedone();.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 482 views
  • 1 like
  • 4 in conversation