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?
Do you really want the remerges which will happen in the first and third SQL?
I am not sure but need to create all those three variables.
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.
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;
So you already get the result you expected?
And if you're looking for code, supply data in usable form, ideally as DATA step cod with DATALINES. Pictures are not usable.
Yes- I already got the expected results. But it took me to run 3 separate proc sql, I want to do it in one.
If more concise code is your goal, you can probably create all variables with one DATA step as opposed to multiple PROC SQL queries .
I am happy to do all those in one data step if possible.
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.
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;
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 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.