Hi @richart I believe there might be some inbuilt procedures within the SAS ETS module should you have time series dates in your real data to fill. Of course, otherwise you can always rely on a datastep-
data have;
infile datalines delimiter=' ';
input group count n;
datalines;
9 1 400
9 2 297
9 3 344
9 4 32
9 5 45
9 6 6
9 7 54
9 8 34
9 9 21
10 1 32
10 2 3
10 3 57
10 4 43
10 5 45
10 6 45
10 7 34
10 8 32
10 11 5
;
data want ;
do until (last.group) ;
set have curobs = _n ;
by group ;
if _c and dif(count) > 1 then do ;
do count = _c + 1 to count - 1 ;
n = 0 ;
output ;
end ;
set have point = _n ;
output ;
end ;
else output ;
_c =count ;
end ;
drop _c ;
run ;
... View more
An addendum to elegant responses, to discern is actually the situation analogous to the function of OR(logic gates) and the corresponding TRUTH tables. Alas, should that be too much to dig in, a better way to understand this -
if x=1 or 2; /*this is what's happening and will always be true*/
such that if and only if X is captured by the SQL optimizer access path
... View more
Hey Xia @Ksharp , Thank you for taking a moment of your time in saying Hi. Very kind of you. I hope all's well with you. At Bank, where I am at are increasingly focused in utilizing C++ for wealth management portfolio. Though, we are largely a northeastern US retail bank, much of the focus and investment has been towards wealth portfolios, consequently salaries are significantly higher. I am gravitating to move there. Basically, C++ generic algorithms rules financial analysis/computations.
W.R.T retail(consumer banking) portfolios, SAS is still there, though that could change to Python with software as a service on AWS. It's easy here and nothing challenging for me, and most certainly for role model geniuses like you, I'd say rather boring. I am betting my next steps in my career by broadening my horizon. Time will tell of course, however for now I am staying put.
Stay safe & Kind Regards
PS Taking this opportunity to say hello to @ballardw @PaigeMiller @Reeza and @FreelanceReinh . Will connect with you folks offline with my career update/other interests :). Cheers!
... View more
data exercise ;
input patient fruit$ ;
cards;
1 Apple
1 Pear
1 Apple
1 Berry
1 Berry
2 Apple
2 Pear
3 Pear
3 Pear
3 Berry
4 Pear
4 Berry
4 Berry
4 Berry
;
run ;
data want ;
if _n_ = 1 then do ;
dcl hash h ( ) ;
h.definekey ('fruit') ;
h.definedata ('_iorc_') ;
h.definedone ( ) ;
end ;
do _n_ = 1 by 1 until (last.patient) ;
set exercise ;
by patient ;
if h.find( ) = 0 then _iorc_ = sum (_iorc_ , 1) ;
else _iorc_ = 1 ;
h.replace( ) ;
end ;
do _n_ = 1 to _n_ ;
set exercise ;
h.find( ) ;
if _iorc_ > 1 then output ;
end ;
h.clear( ) ;
run ;
... View more
For what it's worth-
data exercise ;
input patient fruit$ ;
cards;
1 Apple
1 Pear
1 Apple
1 Berry
1 Berry
2 Apple
2 Pear
3 Pear
3 Pear
3 Berry
4 Pear
4 Berry
4 Berry
4 Berry
;
run ;
proc sql ;
create table want as
select a.*
from exercise a
inner join
(select patient, fruit, count(*) as c from exercise group by patient, fruit) b
on a.patient=b.patient and a.fruit=b.fruit and c > 1 ;
quit ;
... View more
data have;
infile datalines dlm=',';
input ID $ Year Freq;
datalines;
1,2015,2
1,2016,5
1,2016,8
1,2018,6
1,2019,4
1,2019,3
1,2020,7
1,2020,3
2,2018,1
2,2018,3
2,2020,4
2,2021,6
;
data temp/view=temp;
set have;
by id;
if first.id then group=1;
if dif(year)>1 then group+1;
run;
proc sql;
create table want as
select id, year, freq, max(freq) as max_freq
from temp
group by id, group;
quit ;
... View more
Hi @bherrin Thank you for reaching out. Could you please post a sample data of what you "HAVE" that I can copy/paste to my SAS/SQL client and the expected "OUTPUT(WANT)" for me to work with. Thank you!
... View more
proc sql;
create table have as
select *
from sashelp.class
order by sex;
quit;
proc sql;
create table want as
select *, monotonic() - min(monotonic()) + 1 as want
from have
group by sex;
quit;
Name
Sex
Age
Height
Weight
want
Judy
F
14
64.3
90.0
1
Jane
F
12
59.8
84.5
2
Joyce
F
11
51.3
50.5
3
Barbara
F
13
65.3
98.0
4
Carol
F
14
62.8
102.5
5
Mary
F
15
66.5
112.0
6
Louise
F
12
56.3
77.0
7
Alice
F
13
56.5
84.0
8
Janet
F
15
62.5
112.5
9
Philip
M
16
72.0
150.0
1
James
M
12
57.3
83.0
2
Henry
M
14
63.5
102.5
3
John
M
12
59.0
99.5
4
William
M
15
66.5
112.0
5
Alfred
M
14
69.0
112.5
6
Jeffrey
M
13
62.5
84.0
7
Thomas
M
11
57.5
85.0
8
Ronald
M
15
67.0
133.0
9
Robert
M
12
64.8
128.0
10
... View more
