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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
FreelanceReinh
Jade | Level 19

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).

vonsel
Calcite | Level 5

Thanks a lot, both solutions work.

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

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