Hello,
I have the below code which is in a data step but i wanted to know if this was possible to do in a Proc SQL statement?
DATA SCORE; SET WEEKS.TEST; BY SBRID; COUNTER+1; IF FIRST.SBRID THEN COUNTER=1; RUN;
Thanks
Well, it is possible. What you have to remember is that SQL is different from SAS, SAS treats each obsevation step by by step, and if sorted then you can do this calculation. SQL treats observations as a bunch of observations - order is only given by logical sort, not by logical order in a data table. So you can use the monotonic() function like this:
proc sql; create table SCORE as select SBRID, monotonic() as COUNTER from WEEKS.TEST; quit;
However, if you have the data:
SBRID
001
001
001
There is no logical way of telling SQL that the order is:
SBRID COUNTER
001 1
001 2
001 3
Rather than:
SBRID ORDER
001 3
001 1
001 2
Or any other combination. Is there a reason why you need to do this in SQL, generally speaking for SQL an observation count in itself is not really useful, aggregate functions are of more use (sum, count etc.).
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 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.