Combining multiple proc sql

[BayzidurRahman]

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?

[Kurt_Bremser]

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

[BayzidurRahman]

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

[Kurt_Bremser]

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.

[BayzidurRahman]

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;

[Kurt_Bremser]

So you already get the result you expected?

[Kurt_Bremser]

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

[BayzidurRahman]

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

[SusanFarmer]

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

[BayzidurRahman]

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

[SusanFarmer]

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

[BayzidurRahman]

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.

[SusanFarmer]

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.

[Patrick]

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;

[BayzidurRahman]

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;