BookmarkSubscribeRSS Feed
hellind
Quartz | Level 8

An Account once enrolled in a program continues to be enrolled until it gets enrolled in another program

 

HAVE:

Account NumberDateProgram ID
23452397Jan 2021 
23452397Feb 2021337
23452397Mar 2021 
98668953Jan 2021 
98668953Feb 202110180
98668953Mar 2021338
98668953Apr 2021 

 

 

WANT:

Account NumberDateProgram ID
23452397Jan 2021 
23452397Feb 2021337
23452397Mar 2021337
98668953Jan 2021 
98668953Feb 202110180
98668953Mar 2021338
98668953Apr 2021338

 

Assume the data is already sorted.

 

 

3 REPLIES 3
Astounding
PROC Star

The easy way will apply to all variables, not just Program_ID:

 

data want;
   update have (obs=0) have;
   by Account_Number;
   output;
run;
mkeintz
PROC Star

But you can apply it to just selective variables.  Say you want to apply it to program_id only, but you have another variable (EXTRA) that you do NOT want to carry forward to overwrite missing values: 

 

data want;
  update have (obs=0 ) have (keep=account_number program_id);
  by account_number;
  set have (keep=extra);
  output;
run;

 

In this case, I assume that DATE is never missing, so no special treatment is needed for it. 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Kurt_Bremser
Super User

To do it with just SET, see this:

data want;
set have (rename=(program_id=_p)),
by account_number;
retain program_id;
if first.account_number then program_id = "".
program_id = ifc(_p,_p,program_id);
run;

Untested, posted from my tablet.

Adapt the code if program_id is numeric..

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 798 views
  • 2 likes
  • 4 in conversation