I am using PROC SQL to create a table with the distinct variable combinations I am interested in:
PROC SQL; CREATE TABLE want AS SELECT DISTINCT var1, var2, var3 FROM have; QUIT;
However, I would like to add a column to that created table with a running number (and not use a subsequent data step to do so). Is there an elegant way to do that?
Hmm, the moment I run this:
PROC SQL; CREATE TABLE want AS SELECT DISTINCT var1, var2, var3, monotonic() as running_no FROM have; QUIT;
the DISTINCT counts every single running number as a distinct combination of those variables and just gives back the entire dataset "have" with an added running-number-column.
As @LinusH has mentioned, monotonic() couts each observation as its processed. However SQL is not an ordered processing like SAS, it can be affected by many different things internally, such as sorts, distinct, joins etc. I wouldn't recommend using it in this instance, as the numerb assigned to a distinct value is unlikely to be the sequence you intend, unless you happen to know offhand what internal procesuders, and what threads each is assigned to. Why do you need to add this? Am thinking if you do need this, then why not combine it into another step as datastep already has _n_ for position of observation in the dataset, however I always find its better to fix it yourself by hand, e.g.
proc sort data=have nodupkey;
Then use that coded list to merge an identifier onto the data, otherwise if your list changes programmatically as you do now, then your output may change accordingly and further code might not even run. If you can give a proper example of what you are trying to do...
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.
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.