topic Re: How to print obserbations or rownumber for PROC SQL procedure in New SAS User

How to print obserbations or rownumber for PROC SQL procedure

Ashpak — Fri, 26 Feb 2021 23:57:28 GMT

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

Re: How to print obserbations or rownumber for PROC SQL procedure

ballardw — Sat, 27 Feb 2021 00:01:36 GMT

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

Re: How to print obserbations or rownumber for PROC SQL procedure

Ashpak — Sat, 27 Feb 2021 00:06:58 GMT

No Just in the result Query

Re: How to print obserbations or rownumber for PROC SQL procedure

ballardw — Sat, 27 Feb 2021 06:28:43 GMT

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.

Re: How to print obserbations or rownumber for PROC SQL procedure

Kurt_Bremser — Sat, 27 Feb 2021 12:26:10 GMT

Do you actually want the remerge, or do you want a single line per abc,xyz?

Re: How to print obserbations or rownumber for PROC SQL procedure

Ashpak — Mon, 01 Mar 2021 23:50:27 GMT

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.

Re: How to print obserbations or rownumber for PROC SQL procedure

Kurt_Bremser — Tue, 02 Mar 2021 08:33:35 GMT

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.