Hi I have PROC SQL procedure with SELECT SQL code , I would like to print SLNO or observation number when PROC SQL SELECT query executes.
Example
PROC SQL;
SELECT ABC,XYZ, DEP,Sum(SAL)as Sal
from PNQ
where DEP='xxx'
group by ABC,XYZ;
Quit;
I would like to print the results with SL no , don't want to create SAS data set for this, Is there any other approach we can get Select SQL with sl or observation number printed on output results.?
Do you expect this to be the "row number" in the source data set or in the result of the query?
There is an undocumented SAS function called MONOTONIC that will accomplish this. See the following example:
proc sql; create table junk as select sex,age, mean(weight) as mweight, monotonic() as n from sashelp.class group by sex, age ; quit;
Undocumented means that behavior is somewhat unpredictable in some situations and may disappear at any time when SAS upgrades so is really not recommended in production jobs.
Do you actually want the remerge, or do you want a single line per abc,xyz?
I just want normal sql code results with observation slno, example if the PROC SQL procedure returns 10 records then I should get with printed each records with SLno or observation no.
See this short example:
data pnq;
input abc $ xyz $ dep $ sal;
datalines;
A A xxx 1
A A xxx 2
A A Y 5
B B xxx 6
B B Z 7
;
PROC SQL;
SELECT ABC,XYZ, DEP,Sum(SAL)as Sal
from PNQ
where DEP='xxx'
group by ABC,XYZ;
Quit;
Log from the SQL:
84 PROC SQL; 85 SELECT ABC,XYZ, DEP,Sum(SAL)as Sal 86 from PNQ 87 where DEP='xxx' 88 group by ABC,XYZ; NOTE: The query requires remerging summary statistics back with the original data. 89 Quit;
Result:
abc xyz dep Sal A A xxx 3 A A xxx 3 B B xxx 6
As you can see, you get two lines for the first group, instead of only one with the sum. Is this your intention?
No matter what, a result like this with a row number can be done with PROC PRINT. A dataset with a running count is done best in a data step.
Maxim 14: Use the Right Tool.
Most often, SQL is NOT the right tool.
For more help, please supply example data in a data step with datalines (see my code example), and show the exact expected result from this, and declare if you want a report or a dataset.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.