topic Re: data wrangling question in SAS Programming

data wrangling question

dennis_oz — Thu, 20 Aug 2020 14:21:39 GMT

Hi all ,

I have another query . How can I obtain this

I am looking to get the most recent "group" value before the "group" value became "Unknown"

data have;
informat dates date9.;
format dates date9.;
input dates policy status :$8. group $20.;
datalines;
24APR2018 81341802 ACTIVE Silver
17DEC2018 81341802 ACTIVE Gold
14MAR2019 81341802 ACTIVE Gold
25MAR2019 81341802 CEASED Unknown
24JUL2019 81341802 CEASED Unknown
11SEP2019 81341802 CEASED Unknown
24SEP2019 81341802 CEASED Unknown
08JUL2016 81341828 ACTIVE Gold
09JUL2016 81341828 ACTIVE Gold
01APR2017 81341828 CEASED Unknown
01APR2018 81341828 CEASED Unknown
17DEC2018 81341899 ACTIVE Silver
14MAR2019 81341899 ACTIVE Bronze
25MAR2019 81341899 CEASED Unknown
14MAR2019 81346142 ACTIVE Platinum
25MAR2019 81346142 CEASED Unknown
;
run;

want;
14MAR2019 81341802  Gold
09JUL2016 81341828  Gold
14MAR2019 81341899  Bronze
14MAR2019 81346142  Platinum

Re: data wrangling question

PaigeMiller — Thu, 20 Aug 2020 14:18:52 GMT

Explain the logic used to go from HAVE to WANT.

Re: data wrangling question

PeterClemmensen — Thu, 20 Aug 2020 14:23:11 GMT

Please explain the logic..

Re: data wrangling question

dennis_oz — Thu, 20 Aug 2020 14:23:39 GMT

if a policy is "Ceased" I want the last "group" value when the policy was in "Active" state

Re: data wrangling question

PaigeMiller — Thu, 20 Aug 2020 14:29:12 GMT

data want;
    set have;
	prev_date=lag(dates);
	prev_policy=lag(policy);
	prev_status=lag(status);
	prev_group=lag(group);
	if policy=prev_policy and status="CEASED" and prev_status="ACTIVE" then output;
	drop dates policy status group;
	format prev_date date9.;
run;

Re: data wrangling question

novinosrin — Thu, 20 Aug 2020 14:29:36 GMT


 
data have;
informat dates date9.;
format dates date9.;
input dates policy status :$8. group $20.;
datalines;
24APR2018 81341802 ACTIVE Silver
17DEC2018 81341802 ACTIVE Gold
14MAR2019 81341802 ACTIVE Gold
25MAR2019 81341802 CEASED Unknown
24JUL2019 81341802 CEASED Unknown
11SEP2019 81341802 CEASED Unknown
24SEP2019 81341802 CEASED Unknown
08JUL2016 81341828 ACTIVE Gold
09JUL2016 81341828 ACTIVE Gold
01APR2017 81341828 CEASED Unknown
01APR2018 81341828 CEASED Unknown
17DEC2018 81341899 ACTIVE Silver
14MAR2019 81341899 ACTIVE Bronze
25MAR2019 81341899 CEASED Unknown
14MAR2019 81346142 ACTIVE Platinum
25MAR2019 81346142 CEASED Unknown
;
run;
proc format;
 value $gem
 'Unknown'='0'
 other='1';
run;

data want;
 do until(last.group);
  set have;
  by groupformat group notsorted;
  if group ne 'Unknown';
  format group gem.;
 end;
run;

proc datasets lib=work noprint;
 modify want;
 format group;
run;

proc print noobs;run;

Re: data wrangling question

novinosrin — Thu, 20 Aug 2020 14:32:49 GMT

Sorry @dennis_oz I was experimenting the puzzle in my previous before you explained the logic. The Status variable makes it easy-

data want;
 do until(last.status);
  set have;
  by  status notsorted;
  if group ne 'Unknown';
 end;
run;

Re: data wrangling question

PeterClemmensen — Thu, 20 Aug 2020 14:32:52 GMT

Provided your data is representable:

data want(drop=s p);
   merge have
         have(firstobs=2 rename=(status=s policy=p) keep=status policy);
   if policy=p & status ne s & s = "CEASED";
run;

Re: data wrangling question

dennis_oz — Thu, 20 Aug 2020 14:51:32 GMT

hi @novinosrin , cn you explain what " notsorted " does ?

Re: data wrangling question

novinosrin — Thu, 20 Aug 2020 14:56:13 GMT

NOTSORTED is an option in "By group" processing that handles a set of input data that is not ordered in a particular collating sequence rather by the pattern of existing values. In other words, the existing values as-is until changes becomes a group of its own.

Recommended reading: BY GROUP processing

If you have author Ron Cody's book, even better

Re: data wrangling question

dennis_oz — Thu, 20 Aug 2020 15:47:36 GMT


 
data have;
informat dates date9.;
format dates date9.;
input dates policy status :$8. group $20.;
datalines;
24APR2018 81341802 ACTIVE Silver
17DEC2018 81341802 ACTIVE Gold
14MAR2019 81341802 ACTIVE Gold
08JUL2016 81341828 ACTIVE Gold
09JUL2016 81341828 ACTIVE Gold
01APR2017 81341828 CEASED Unknown
01APR2018 81341828 CEASED Unknown
17DEC2018 81341899 ACTIVE Silver
14MAR2019 81341899 ACTIVE Bronze
25MAR2019 81341899 CEASED Unknown
14MAR2019 81346142 ACTIVE Platinum
25MAR2019 81346142 CEASED Unknown
14MAR2019 81346142 ACTIVE Platinum
25MAR2019 81346142 SUSPENDED Platinum
;
run;

Hi @novinosrin ,

suppose the data is in the above format ..

1) where a policy can be "active" as well and not have group as 'Unknown' .

2) suppose policy can be in "suspended" and group will have some value but not "unknown" then how can we handle this

Re: data wrangling question

novinosrin — Thu, 20 Aug 2020 15:59:12 GMT

Hi @dennis_oz Can you include your expected result plz to avoid guesses-

Are you after this?


data have;
informat dates date9.;
format dates date9.;
input dates policy status :$8. group $20.;
datalines;
24APR2018 81341802 ACTIVE Silver
17DEC2018 81341802 ACTIVE Gold
14MAR2019 81341802 ACTIVE Gold
08JUL2016 81341828 ACTIVE Gold
09JUL2016 81341828 ACTIVE Gold
01APR2017 81341828 CEASED Unknown
01APR2018 81341828 CEASED Unknown
17DEC2018 81341899 ACTIVE Silver
14MAR2019 81341899 ACTIVE Bronze
25MAR2019 81341899 CEASED Unknown
14MAR2019 81346142 ACTIVE Platinum
25MAR2019 81346142 CEASED Unknown
14MAR2019 81346142 ACTIVE Platinum
25MAR2019 81346142 SUSPENDED Platinum
;
run;

data want;
 set have;
 by status notsorted;
 if last.status and status='ACTIVE';
run;

Re: data wrangling question

dennis_oz — Thu, 20 Aug 2020 16:22:39 GMT

my bad . so sorry . my expected output is .

want;
14MAR2019 81341802 ACTIVE Gold
14MAR2019 81341899 ACTIVE Bronze
14MAR2019 81346142 ACTIVE Platinum
25MAR2019 81346142 SUSPENDED Platinum

I always want a "group" value and it should never be "unknown" .