topic First.id result with proc sql in SAS Programming

First.id result with proc sql

Riteshdell — Thu, 13 Aug 2020 11:44:16 GMT

hello Experts,

Is there any way to get First.ID result with proc sql.

Please let me know.

data readin;
input ID Name $ Score;
cards;
1     David   45
1     David   74
2     Sam     45
2     Ram     54
3     Bane    87
3     Mary    92
3     Bane    87
4     Dane    23
5     Jenny   87
5     Ken     87
6     Simran  63
8     Priya   72
;
run;

PROC SORT DATA = READIN;
BY ID;
RUN;

DATA READIN1;
SET READIN;
BY ID;
First_ID= First.ID;
RUN;

Expected output by proc sql method.

Re: First.id result with proc sql

Tom — Thu, 13 Aug 2020 11:55:25 GMT

Not really. SQL does not have a concept of row orders. Newer versions of SQL had to invent a whole complex system of "windowing" functions to implement that. But PROC SQL does not support those more modern constructs.

Do you have ordering variable you can use to order the observations within the group? Is it unique? If so then use that.

For example lets make one on your example data by adding a variable called ROW so we can demonstrate.

data readin_ordered;
  set readin;
  by id;
  if first.id then row=0;
  row+1;
run;

proc sql ;
create table want as
  select *,row=min(row) as first_id
  from readin_ordered
  group by id
  order by id,row
;
quit;

Obs    ID    Name      Score    row    first_id

  1     1    David       45      1         1
  2     1    David       74      2         0
  3     2    Sam         45      1         1
  4     2    Ram         54      2         0
  5     3    Bane        87      1         1
  6     3    Mary        92      2         0
  7     3    Bane        87      3         0
  8     4    Dane        23      1         1
  9     5    Jenny       87      1         1
 10     5    Ken         87      2         0
 11     6    Simran      63      1         1
 12     8    Priya       72      1         1

Re: First.id result with proc sql

Kurt_Bremser — Thu, 13 Aug 2020 11:56:24 GMT

You can do it by using the undocumented MONOTONIC() function:

proc sql;
create table want (drop=mn) as
  select
    a.*,
    monotonic() as mn,
    case
      when calculated mn = min(calculated mn)
      then 1
      else 0
    end as first_id
  from readin a
  group by id
  order by id, mn
;
quit;

Bur compare this to the extremely simple and easy to understand data step code. Maxim 14: Use the Right Tool.

Re: First.id result with proc sql

Riteshdell — Thu, 13 Aug 2020 12:25:17 GMT

Thank you , @Kurt_Bremser