topic Translate data step to sql in SAS Programming

Translate data step to sql

nickspencer — Mon, 21 Feb 2022 17:11:27 GMT

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

Re: Translate data step to sql

yabwon — Mon, 21 Feb 2022 17:47:44 GMT

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

Re: Translate data step to sql

Tom — Mon, 21 Feb 2022 17:55:20 GMT

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.

Re: Translate data step to sql

novinosrin — Mon, 21 Feb 2022 17:58:06 GMT

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

Re: Translate data step to sql

yabwon — Mon, 21 Feb 2022 18:58:24 GMT

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

Bart

Re: Translate data step to sql

Cynthia_sas — Mon, 21 Feb 2022 19:58:26 GMT

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

Re: Translate data step to sql

Ksharp — Tue, 22 Feb 2022 12:09:12 GMT

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;

Re: Translate data step to sql

Kurt_Bremser — Tue, 22 Feb 2022 12:30:07 GMT

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.

Re: Translate data step to sql

novinosrin — Tue, 22 Feb 2022 15:18:26 GMT

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

Re: Translate data step to sql

yabwon — Tue, 22 Feb 2022 15:22:24 GMT

ODS to the rescue! 😀 😀

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

Bart

Re: Translate data step to sql

Kurt_Bremser — Tue, 22 Feb 2022 16:56:14 GMT

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

Re: Translate data step to sql

Ksharp — Wed, 23 Feb 2022 12:26:04 GMT

You are welcome ! 🙂

Re: Translate data step to sql

Ksharp — Wed, 23 Feb 2022 12:27:01 GMT

Agree.