Translate data step to sql

[nickspencer]

Hello everyone,
I am trying to translate the following code from data step to sql. How do I write the following code in SQL?


proc sort data=dsn;
by ID name;
run;

data final;
Set dsn;
by ID Name;
Class+1;
if first.ID then class=1;
run;

Appreciate any help.

Thanks,
Nick

[yabwon]

You can try it with Cartesian product [EDIT: assuming that name doesn't have duplicates in groups]:

data dsn;
  do ID = "C", "B", "A";
    do name = "John1", "John5", "John3";
      output;
    end;
  end;
run;


proc sql;
  create table temp as
  select a.ID, a.name, count(1) as Class
  from dsn as a
     , dsn as b

  where a.ID = b.ID and a.name >= b.name 
  group by a.ID, a.name
  order by a.ID, a.name
  ;

quit;

but why to use SQL in the first place?

Bart

[Tom]

You can't.  Why?

If you want to be able to do that in SQL then you will need an implementation of SQL that supports windowing functions.  PROC SQL only supports ANSI 1992 version of SQL which pre-dates the definition of SQL windowing functions.

[novinosrin]

proc sql;
  create table have as
  select *
  from sashelp.class
  order by sex;
quit;

proc sql;
 create table want as
 select *, monotonic() - min(monotonic()) + 1 as want
 from have
 group by sex;
quit;

Name	Sex	Age	Height	Weight	want
Judy	F	14	64.3	90.0	1
Jane	F	12	59.8	84.5	2
Joyce	F	11	51.3	50.5	3
Barbara	F	13	65.3	98.0	4
Carol	F	14	62.8	102.5	5
Mary	F	15	66.5	112.0	6
Louise	F	12	56.3	77.0	7
Alice	F	13	56.5	84.0	8
Janet	F	15	62.5	112.5	9
Philip	M	16	72.0	150.0	1
James	M	12	57.3	83.0	2
Henry	M	14	63.5	102.5	3
John	M	12	59.0	99.5	4
William	M	15	66.5	112.0	5
Alfred	M	14	69.0	112.5	6
Jeffrey	M	13	62.5	84.0	7
Thomas	M	11	57.5	85.0	8
Ronald	M	15	67.0	133.0	9
Robert	M	12	64.8	128.0	10

[yabwon]

I don't trust monotonic(), newer saw it documented 😉😉

Bart

[Cynthia_sas]

Hi:
That's a good instinct (not to trust monotonic) -- there's a Tech Support note out there that explains monotonic might give undesirable results. If I can find it, I'll post the note here. https://support.sas.com/techsup/notes/v8/15/138.html
Cynthia

[Ksharp · Accepted solution]

yabwon,

How about this one ?

ods select none;
ods output sql_results=temp;
proc sql number;
  select *
  from sashelp.class
  order by sex ;
quit;
ods select all;

proc sql;
 create table want as
 select *, row - min(row) + 1 as want
 from temp
 group by sex
 order by row;
quit;

[novinosrin]

@Ksharp  owe you a beer. kudos! You are as good as Guru(king)  @data_null__  in using the documentation.  Thank you. 

[Ksharp]

You are welcome ! 🙂

[yabwon]

ODS to the rescue! 😀 😀

But still think, and agree with @Kurt_Bremser, that this is a job for data step.

Bart

[Kurt_Bremser]

With anything but small data, the ODS detour and SQL will most probably perform like watching the paint dry.

[Ksharp]

Agree.

[Kurt_Bremser]

Within SAS, the tool for such a task is the data step, period.

I would write it a little differently:

data final;
set dsn;
by ID Name;
if first.ID
then class = 1;
else class + 1;
run;

If your intent is moving the code to a DBMS, then you should study that DBMS's documentation, or get help from a website that deals with that specific DB.

As long as you work with SAS, stay with the data step.