BookmarkSubscribeRSS Feed
BayzidurRahman
Obsidian | Level 7

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?

21 REPLIES 21
BayzidurRahman
Obsidian | Level 7

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

Kurt_Bremser
Super User

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
Obsidian | Level 7

BayzidurRahman_0-1693381291256.png

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;


BayzidurRahman_0-1693381398330.png

 

 

BayzidurRahman
Obsidian | Level 7

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
SAS Employee

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
Obsidian | Level 7

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

SusanFarmer
SAS Employee
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
Obsidian | Level 7

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
SAS Employee
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
Opal | Level 21

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
Obsidian | Level 7

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;

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

LIBNAME 101

Follow along as SAS technical trainer Dominique Weatherspoon expertly answers all your questions about SAS Libraries.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 21 replies
  • 3815 views
  • 2 likes
  • 4 in conversation