Help using Base SAS procedures

how to create new group/type variable

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

how to create new group/type variable

[ Edited ]

I want to create the new group/type variable. I have variables ID and PRICE and want to create the variable COLL.

So for each ID i want the type variable COLL, to check whether the price is still the same. So my final table will be:

 

obrazok.png

How can I do it in proc data or proc sql?


Accepted Solutions
Solution
‎03-02-2016 06:34 AM
Super User
Super User
Posts: 7,974

Re: how to create new group/type variable

Here you go, do not for future posts, put test data in the form of a datastep, as given below:

data have;
  input id price;
datalines;
1 1000
1 1000
1 2000
2 1000
2 1000
3 5000
3 5000
3 7000
;
run;

data want;
  set have;
  by id price;
  retain coll;
  if first.id then coll=1;
  else if first.price then coll=coll+1;
run;

View solution in original post


All Replies
Solution
‎03-02-2016 06:34 AM
Super User
Super User
Posts: 7,974

Re: how to create new group/type variable

Here you go, do not for future posts, put test data in the form of a datastep, as given below:

data have;
  input id price;
datalines;
1 1000
1 1000
1 2000
2 1000
2 1000
3 5000
3 5000
3 7000
;
run;

data want;
  set have;
  by id price;
  retain coll;
  if first.id then coll=1;
  else if first.price then coll=coll+1;
run;
Trusted Advisor
Posts: 1,117

Re: how to create new group/type variable

Hi @vonsel,

 

If prices can change in both directions (as is the case for ID 3 in your sample data), you need to add the NOTSORTED option to the BY statement: by id price notsorted;

Here's a minor variant of RW9's solution:

data want;
set have;
by id price notsorted;
coll+first.price;
if first.id then coll=1;
run;

Obviously, the numbering in COLL depends on the sort order of the observations. So, I hope you have an additional variable in your real data which determines the sort order within each ID (or you have good reason to believe that the existing sort order is correct).

New Contributor
Posts: 2

Re: how to create new group/type variable

Posted in reply to FreelanceReinhard

Thanks a lot, both solutions work.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 308 views
  • 0 likes
  • 3 in conversation