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:
How can I do it in proc data or proc sql?
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;
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;
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).
Thanks a lot, both solutions work.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.