group by without sql

Accepted Solution Solved
Reply
Valued Guide
Posts: 858
Accepted Solution

group by without sql

I would like to roll up the default_flag to the acct level, so if any instrument has a default then anything under that corresponding acct would also have a default_flag of 1.  Any help will be appreciated.

Thank You and Happy New Year,

data data;

infile cards;

input acct instrument default;

cards;

1 1 1

1 2 .

1 3 .

2 1 .

2 2 .

2 3 .

;

run;

data have;

set data;

if default = 1 then default_flag = 1;

run;


Accepted Solutions
Solution
‎12-31-2014 11:41 PM
Respected Advisor
Posts: 4,646

Re: group by without sql

A simple double DOW design does the trick:

data have;

input acct instrument default;

datalines;

1 1 .

1 2 1

1 3 .

2 1 .

2 2 .

2 3 .

;

data want;

do until(last.acct);

    set have; by acct;

    def = coalesce(def, default);

    end;

do until(last.acct);

    set have; by acct;

    default = def;

    output;

    end;

drop def;

run;

PG

PG

View solution in original post


All Replies
PROC Star
Posts: 7,363

Re: group by without sql

Not sure what you are trying to do, so this is just a guess:

data want;

  do until(last.acct);

    set data;

    by acct;

    if first.acct then do;

      if default eq 1 then default_flag=default;

      else call missing(default_flag);

    end;

  end;

  do until (last.acct);

    set data;

    by acct;

    output;

  end;

run;

Valued Guide
Posts: 858

Re: group by without sql

Arthur, thank you very much.  Is there another solution if the data is not sorted by default?

Frequent Contributor
Posts: 115

Re: group by without sql

data want;

set have;

by acct;

retain default_flag;

if first.acct and default=1 then default_flag=1;

else if first.acct and default=. then default_flag=.;

run;

Valued Guide
Posts: 858

Re: group by without sql

Naveen,

I made a mistake in my initial request.  Here is what I am looking for but this doesn't seem to work.  I do not want another field / flag but rather to maintain the default value throughout:

data want;

set data;

by acct;

retain default;

if first.acct and default=1 then default=1;

else if first.acct and default=. then default=.;

run;

I thought the translation would work but it does not, can you provide a solution?

Frequent Contributor
Posts: 115

Re: group by without sql

is this what you want?/*take ur first dataset as have*/

data want;

set have;

by acct;

retain default_flag;

if first.acct and default=1 then default_flag=1;

else if first.acct and default=. then default_flag=.;

if default ne default_flag then default=default_flag;

drop default_flag;

run;

Respected Advisor
Posts: 3,890

Re: group by without sql

One way to go:

data have;

  infile cards;

  input acct instrument default;

  cards;

1 1 .

1 2 1

1 3 .

2 1 .

2 2 .

2 3 .

;

run;

data want(drop=_Smiley Happy;

  set have;

  if _n_=1 then

    do;

      dcl hash h1(dataset:'have(keep=acct default where=(default=1))');

      _rc=h1.defineKey('acct');

      _rc=h1.defineData('default');

      _rc=h1.defineDone();

    end;

  _rc=h1.find();

run;

Frequent Contributor
Posts: 115

Re: group by without sql

data data;

input acct instrument default;

datalines;

1 1 .

1 2 1

1 3 .

2 1 .

2 2 .

2 3 .

;

data want;

set data;

by acct;

retain default_flag;

if first.acct then default_flag=default;

if default=1 then default_flag=default;

else if default ne default_flag then default=default_flag;

if last.acct;

run;

data final(rename=(default_flag=default));

merge want data;

by acct;

drop default;

run;

Valued Guide
Posts: 858

Re: group by without sql

Like this:

data data;

infile cards;

input acct instrument default;

cards;

1 1 .

1 2 1

1 3 .

2 1 .

2 2 .

2 3 .

;

run;

PROC Star
Posts: 7,363

Re: group by without sql

Basically just the same code but adjusted to account for your needs:

data want;

  do until(last.acct);

    set data;

    by acct;

    if first.acct then call missing(default_flag);

    if default eq 1 then default_flag=default;

  end;

  do until (last.acct);

    set data;

    by acct;

    output;

  end;

run;

PROC Star
Posts: 7,363

Re: group by without sql

I think that the following account for both of your new conditions:

data want (drop=default rename=(default_flag=default));

  do until(last.acct);

    set data;

    by acct;

    if first.acct then call missing(default_flag);

    if default eq 1 then default_flag=default;

  end;

  do until (last.acct);

    set data;

    by acct;

    output;

  end;

run;

Trusted Advisor
Posts: 1,204

Re: group by without sql

proc sql;

create table want as

select *,sum(default) as default_flag

from data group by acct;

quit;

Solution
‎12-31-2014 11:41 PM
Respected Advisor
Posts: 4,646

Re: group by without sql

A simple double DOW design does the trick:

data have;

input acct instrument default;

datalines;

1 1 .

1 2 1

1 3 .

2 1 .

2 2 .

2 3 .

;

data want;

do until(last.acct);

    set have; by acct;

    def = coalesce(def, default);

    end;

do until(last.acct);

    set have; by acct;

    default = def;

    output;

    end;

drop def;

run;

PG

PG
Super User
Posts: 9,681

Re: group by without sql

data data;
input acct instrument default;
datalines;
1 1 .
1 2 1
1 3 .
2 1 .
2 2 .
2 3 .
; 
data want ;
 merge data(drop=default) data(keep=acct default where=(default=1) );
 by acct;
run;

Xia Keshan

Respected Advisor
Posts: 4,646

Re: group by without sql

This is very clever !

PG

PG
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 15 replies
  • 780 views
  • 6 likes
  • 7 in conversation