- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Do you really want the remerges which will happen in the first and third SQL?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I am not sure but need to create all those three variables.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
So you already get the result you expected?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
And if you're looking for code, supply data in usable form, ideally as DATA step cod with DATALINES. Pictures are not usable.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Yes- I already got the expected results. But it took me to run 3 separate proc sql, I want to do it in one.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
If more concise code is your goal, you can probably create all variables with one DATA step as opposed to multiple PROC SQL queries .
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I am happy to do all those in one data step if possible.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;