topic Re: Combining multiple proc sql in Programming 1 and 2

Combining multiple proc sql

BayzidurRahman — Wed, 30 Aug 2023 05:04:28 GMT

Hi,
I am using the following 3 proc sql to generate 3

proc sql;
create table test3 as 
  select *, SUM( count ) as dur
      from test2
  group by id,gr
;
quit;

proc sql;
create table test4 as 
  select *, case 
    when dur=11 then 1
    end as stay
		    from test3
;
quit;

proc sql;
create table test5 as 
  select *, max(stay) as staykeep
	    from test4
		group by id
;
quit;

variables from a dataset. Can you please suggest how can I do that under a single proc sql?

Re: Combining multiple proc sql

Kurt_Bremser — Wed, 30 Aug 2023 07:30:59 GMT

Do you really want the remerges which will happen in the first and third SQL?

Re: Combining multiple proc sql

BayzidurRahman — Wed, 30 Aug 2023 07:32:53 GMT

I am not sure but need to create all those three variables.

Re: Combining multiple proc sql

Kurt_Bremser — Wed, 30 Aug 2023 07:35:06 GMT

You create a lot more variables, as you use the asterisk.

Please supply an example for the data you start with, and what you want to get as aresult.

Re: Combining multiple proc sql

BayzidurRahman — Wed, 30 Aug 2023 07:43:42 GMT

proc sql;
create table test3 as 
  select *, SUM( count ) as dur
      from test2
  group by id,gr
;
quit;

proc sql;
create table test4 as 
  select *, case 
    when dur>11 then 1
    end as stay
		    from test3
;
quit;

proc sql;
create table test5 as 
  select *, max(stay) as staykeep
	    from test4
		group by id
;
quit;

Re: Combining multiple proc sql

Kurt_Bremser — Wed, 30 Aug 2023 08:00:13 GMT

So you already get the result you expected?

Re: Combining multiple proc sql

Kurt_Bremser — Wed, 30 Aug 2023 08:01:57 GMT

And if you're looking for code, supply data in usable form, ideally as DATA step cod with DATALINES. Pictures are not usable.

Re: Combining multiple proc sql

BayzidurRahman — Wed, 30 Aug 2023 11:22:32 GMT

Yes- I already got the expected results. But it took me to run 3 separate proc sql, I want to do it in one.

Re: Combining multiple proc sql

SusanFarmer — Wed, 30 Aug 2023 20:39:07 GMT

If more concise code is your goal, you can probably create all variables with one DATA step as opposed to multiple PROC SQL queries .

Re: Combining multiple proc sql

BayzidurRahman — Wed, 30 Aug 2023 22:48:38 GMT

I am happy to do all those in one data step if possible.

Re: Combining multiple proc sql

SusanFarmer — Wed, 30 Aug 2023 23:09:59 GMT

I would use FIRST. LAST. variables with grouped data. This URL should give you some ideas, if you haven't used them before: https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/lepg/p181g1p4bw3phkn1vt5p67xvynd5.htm

Re: Combining multiple proc sql

BayzidurRahman — Wed, 30 Aug 2023 23:14:30 GMT

Thanks for the link. I have some experience with the first. and last. This situation is a bit more complicated as i need to use multiple BY statemants with different number of BY variables.

Re: Combining multiple proc sql

SusanFarmer — Wed, 30 Aug 2023 23:19:03 GMT

It looks as if your grouping is primarily on ID with a secondary on GR. That requires only one sort BY ID GR which produces 4 grouping variables: FIRST.ID, LAST.ID, FIRST.GR and LAST.GR. You can use process on the ID GR groups plus the ID only group in one step.

Re: Combining multiple proc sql

Patrick — Wed, 30 Aug 2023 23:32:12 GMT

It really helps us help you if you provide sample have data via a fully working data step as done in below code that should return what you're asking for.

Please note that with the databases I know when using a SQL group by the select clause may only contain the grouping and aggregation variables. That you can have other variables in the select clause is a SAS SQL flavor special.

data test2;
  input id gr count df date:date9.;
  format date date9.;
  datalines;
1 1 1 1 01jan2022
1 1 1 1 02jan2022
2 1 1 1 03jan2022
2 2 1 1 04jan2022
2 3 9 1 05jan2022
2 3 2 1 06jan2022
;


proc sql;
/*  create table test5 as*/
  select 
    l.*
    ,r.dur
    ,r.stay
    ,max(r.stay) as staykeep
  from
    test2 l
    inner join
    (
      select 
        id
        ,gr
        ,sum(count) as dur
        ,case
          when sum(count) = 11 then 1
          else 0
          end
          as stay 
      from test2
      group by id,gr
    ) r
    on l.id=r.id and l.gr=r.gr
    group by l.id
    order by id,gr,date
  ;
quit;

Re: Combining multiple proc sql

BayzidurRahman — Wed, 30 Aug 2023 23:35:40 GMT

Hi, Sorry for the hassle. I posted a complete have data with working data step but it didn't go through. Please see it below.

data test2;
input obs id gr dif count;
datalines;
1 1 1 1 1 
2 1 1 1 1 
3 1 1 1 1 
4 1 1 1 1 
5 1 1 1 1 
6 1 1 1 1 
7 1 1 1 1 
8 1 1 1 1 
9 1 1 1 1 
10 1 1 1 1 
11 1 1 1 1 
12 1 1 1 1 
13 1 1 1 1 
14 1 1 1 1 
15 1 2 3 1 
16 1 2 1 1 
17 1 2 1 1 
18 1 2 1 1 
19 1 2 1 1 
20 1 2 1 1 
21 1 2 1 1 
22 1 2 1 1 
23 1 2 1 1 
24 2 1 1 1 
25 2 1 1 1 
26 2 1 1 1 
27 2 1 1 1 
28 2 1 1 1 
29 2 1 1 1 
30 2 1 1 1 
31 2 2 6 1 
32 2 2 1 1 
33 2 2 1 1 
34 2 2 1 1 
35 2 2 1 1 
36 2 2 1 1 
37 2 2 1 1 
38 2 2 1 1 
; run;

Re: Combining multiple proc sql

Patrick — Wed, 30 Aug 2023 23:42:44 GMT

Here the SQL that works for your sample data.

proc sql;
/*  create table test5 as*/
  select 
    l.*
    ,r.dur
    ,r.stay
    ,max(r.stay) as staykeep
  from
    test2 l
    inner join
    (
      select 
        id
        ,gr
        ,sum(count) as dur
        ,case
          when sum(count) = 11 then 1
          else 0
          end
          as stay 
      from test2
      group by id,gr
    ) r
    on l.id=r.id and l.gr=r.gr
    group by l.id
    order by obs,id,gr
  ;
quit;

Re: Combining multiple proc sql

BayzidurRahman — Thu, 31 Aug 2023 00:36:03 GMT

HI,

Thanks for the code. Would you please explain what do the 1. r. etc mean? My working dataset is quite big (~0.5 billion rows) and I found proc sql performing much slower than a data step. Is there any way to do the same in a single data step?

Re: Combining multiple proc sql

Patrick — Thu, 31 Aug 2023 00:57:38 GMT

@BayzidurRahman wrote:

HI,

Thanks for the code. Would you please explain what do the 1. r. etc mean? My working dataset is quite big (~0.5 billion rows) and I found proc sql performing much slower than a data step. Is there any way to do the same in a single data step?

If this is about performance then you should have said so from the beginning. The SQL does internal sorting which is what impacts on performance.

When it comes to performance the details matter!

- Is the source data already sorted by id and gr?

- Are id and gr also numerical variables in your actual data?

- How much memory do you have available in your session? (proc options group=memory;run;)

- Do you really need variables dur and stay or would staykeep suffice?

Re: Combining multiple proc sql

BayzidurRahman — Thu, 31 Aug 2023 01:02:47 GMT

Hi Patrick,

Please see below. I didn't realize before that proc sql would take that long.

Is the source data already sorted by id and gr? No

- Are id and gr also numerical variables in your actual data? Yes

- How much memory do you have available in your session? (proc options group=memory;run;)

SAS (r) Proprietary Software Release 9.4 TS1M8

Group=MEMORY
SORTSIZE=1073741824
Specifies the amount of memory that is available to the SORT procedure.
SUMSIZE=0 Specifies a limit on the amount of memory that is available for data
summarization procedures when class variables are active.
MAXMEMQUERY=0 Specifies the maximum amount of memory that is allocated for procedures.
MEMBLKSZ=16777216 Specifies the memory block size for Windows memory-based libraries.
MEMMAXSZ=2147483648
Specifies the maximum amount of memory to allocate for using memory-based
libraries.
LOADMEMSIZE=0 Specifies a suggested amount of memory that is needed for executable
programs loaded by SAS.
MEMSIZE=2147483648
Specifies the limit on the amount of virtual memory that can be used during
a SAS session.
REALMEMSIZE=0 Specifies the amount of real memory SAS can expect to allocate.

- Do you really need variables dur and stay or would staykeep suffice? Yes, I need them

Re: Combining multiple proc sql

Patrick — Thu, 31 Aug 2023 03:47:31 GMT

Given you need to "look ahead" I wouldn't know how to get this done with a single pass through the data nor how to avoid sorting.

Below what I could come up with.

data have;
  input obs id gr dif count;
datalines;
1 1 1 1 1 
2 1 1 1 1 
3 1 1 1 1 
4 1 1 1 1 
5 1 1 1 1 
6 1 1 1 1 
7 1 1 1 1 
8 1 1 1 1 
9 1 1 1 1 
10 1 1 1 1 
11 1 1 1 1 
15 1 2 3 1 
16 1 2 1 1 
17 1 2 1 1 
18 1 2 1 1 
19 1 2 1 1 
20 1 2 1 1 
21 1 2 1 1 
22 1 2 1 1 
23 1 2 1 1 
24 2 1 1 1 
25 2 1 1 1 
26 2 1 1 1 
27 2 1 1 1 
28 2 1 1 1 
29 2 1 1 1 
30 2 1 1 1 
31 2 2 6 1 
32 2 2 1 1 
33 2 2 1 1 
34 2 2 1 1 
35 2 2 1 1 
36 2 2 1 1 
37 2 2 1 1 
38 2 2 1 1 
;

proc sort data=have out=inter;
  by id gr;
run;

options fullstimer;
data want;
  if _n_=1 then
    do;
      length dur stay 8;
      dcl hash h1(hashexp:5);
      h1.defineKey('id','gr');
      h1.defineData('dur','stay');
      h1.defineDone();
    end;

  retain staykeep dur;
  dur=0;
  staykeep=0;

  do until(last.id);
    set inter;
    by id gr;

    dur+count;
    if last.gr then
      do;
        stay= (dur=11);
        if stay=1 then staykeep=1;
        _rc=h1.add();
        dur=0;
      end;
  end;

  last.id=0;
  do until(last.id);
    set inter;
    by id;
    _rc=h1.find();
    output;
  end;

  _rc=h1.clear();
  drop _rc;
run;

proc print data=want;
run;