topic Re: sort by multiple variable in SAS Programming

sort by multiple variable

jojozheng — Tue, 18 Sep 2018 18:33:54 GMT

Hi everyone,

I have a dataset after I sorted by id date, results looks like below:

date id flag

JAN 1 0

FEB 1 0

JAN 2 1

FEB 2 1

JAN 3 0

FEB 3 1

I want to see: first flag=1 group by id date.

date id flag flag1

JAN 1 0

FEB 1 0

JAN 2 1 1

FEB 2 1

JAN 3 0

FEB 3 1 1

My code failed:

data test;

set old;

by id date flag;

if first.flag=1 then flag1=1;

anyone knows how to get data I want? Thank you so much!

Re: sort by multiple variable

novinosrin — Tue, 18 Sep 2018 18:39:12 GMT

are you missing

retain flag1;

Re: sort by multiple variable

jojozheng — Tue, 18 Sep 2018 18:42:59 GMT

no. I just want to denote every first flag=1 corrurance and let it be flag1=1.

Re: sort by multiple variable

Astounding — Tue, 18 Sep 2018 18:43:49 GMT

Try it this way:

data want;

set have;

by id;

retain dummy;

if first.id then dummy=flag;

else dummy + flag;

if dummy=1 then do;

flag1=1;

dummy=2;

end;

drop dummy;

run;

Re: sort by multiple variable

novinosrin — Tue, 18 Sep 2018 18:44:21 GMT

is this your want?

date id flag flag1

JAN 1 0

FEB 1 0

JAN 2 1 1

FEB 2 1

JAN 3 0

FEB 3 1 1

Re: sort by multiple variable

jojozheng — Tue, 18 Sep 2018 18:50:36 GMT

yes

Re: sort by multiple variable

novinosrin — Tue, 18 Sep 2018 19:03:58 GMT

data have;
input date $   id     flag;
cards;
JAN    1      0
FEB    1      0
JAN    2      1
FEB    2      1
JAN    3      0
FEB    3      1
;

data want;

set have;

by id  flag notsorted ;

if first.flag and flag=1 then flag1=1; 
run;

Re: sort by multiple variable

Astounding — Tue, 18 Sep 2018 19:17:14 GMT

That's fine as long as you have a maximum of 2 months per ID. Once you hit 3 months per ID, you have to account for a flag pattern like 1 0 1.

Re: sort by multiple variable

jojozheng — Tue, 18 Sep 2018 19:32:38 GMT

Thank you @novinosrin
result from your code
date id flag flag1

JAN 1 0

FEB 1 0

JAN 2 1 1

FEB 2 1 1

JAN 3 0

FEB 3 1 1

but how to not retain 1 for every id, I just need first flag=1 to be denoted. Thanks!

Re: sort by multiple variable

jojozheng — Tue, 18 Sep 2018 19:46:05 GMT

I have a dataset with million dataset, I just set an example for the situation I try to get.

Re: sort by multiple variable

mkeintz — Tue, 18 Sep 2018 23:47:45 GMT

This one uses virtually the same logic as @Astounding. I put it in for variety, and a possible unreasonable regard for compactness.

data have;
input date $   id     flag;
cards;
JAN    1      0
FEB    1      0
JAN    2      1
FEB    2      1
JAN    3      0
FEB    3      1
;

data want (drop=n_ones);
  set have;
  by id flag;

  if first.id then n_ones=0;
  n_ones+flag;
  flag1= ifn(flag=1 and n_ones=1,1,0);
run;

Re: sort by multiple variable

jojozheng — Wed, 19 Sep 2018 13:24:30 GMT

Thank you!