BookmarkSubscribeRSS Feed
Ashpak
Calcite | Level 5

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

 

 

 

6 REPLIES 6
ballardw
Super User

Do you expect this to be the "row number" in the source data set or in the result of the query?

Ashpak
Calcite | Level 5
No Just in the result Query
ballardw
Super User

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.

Ashpak
Calcite | Level 5

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.

Kurt_Bremser
Super User

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.

SAS Innovate 2025: Register Now

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!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 6 replies
  • 1230 views
  • 0 likes
  • 3 in conversation