Hi....I am trying to update the missing entries for the variables Status, Program Division for each StudentID based on the entries from the corresponding variables where the Term is the same or previous Term which is the closest to the Term with the missing entries. Any suggestions would be appreciated. Thanks.
data list9999;
length StudentID 8 Status $ 8 Program $ 18 Division $ 12 Term 8;
format StudentID BEST12. Status $CHAR8. Program $CHAR18. Division $CHAR12. Term BEST12.;
informat StudentID BEST12. Status $CHAR8. Program $CHAR18. Division $CHAR12. Term BEST12.;
infile datalines4 DLM='7F'x missover dsd;
input StudentID : BEST32. Status : $CHAR8. Program : $CHAR18. Division : $CHAR12. Term : BEST32.;
datalines4;
100068 AutoBodyRepairs CC3LRSD 46
100068 AutoBodyRepairs CC3LRSD 48
100068 ADMITTED IndustrialWelding CC4PostSec 67
100068 ADMITTED IndustrialWelding CC4PostSec 68
100068 ADMITTED IndustrialWelding CC4PostSec 69
100068 ADMITTED IndustrialWelding CC4PostSec 70
100068 ADMITTED IndustrialWelding CC4PostSec 71
100068 72
100068 73
100068 74
;;;;
Want:
StudentID |
Status |
Program |
Division |
Term |
100068 |
AutoBodyRepairs |
CC3LRSD |
46 |
|
100068 |
AutoBodyRepairs |
CC3LRSD |
48 |
|
100068 |
ADMITTED |
IndustrialWelding |
CC4PostSec |
67 |
100068 |
ADMITTED |
IndustrialWelding |
CC4PostSec |
68 |
100068 |
ADMITTED |
IndustrialWelding |
CC4PostSec |
69 |
100068 |
ADMITTED |
IndustrialWelding |
CC4PostSec |
70 |
100068 |
ADMITTED |
IndustrialWelding |
CC4PostSec |
71 |
100068 |
ADMITTED |
IndustrialWelding |
CC4PostSec |
72 |
100068 |
ADMITTED |
IndustrialWelding |
CC4PostSec |
73 |
100068 |
ADMITTED |
IndustrialWelding |
CC4PostSec |
74 |
|
|
|
|
|
|
||||
|
|
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
||
|
Like this?
data WANT;
set HAVE;
if STATUS ^=' ' then PREV_STATUS =STATUS ;
if PROGRAM ^=' ' then PREV_PROGRAM =PROGRAM ;
if DIVISION^=' ' then PREV_DIVISION=DIVISION;
retain PREV: ;
if STATUS =' ' and lag(STUDENTID)=STUDENTID then STATUS =PREV_STATUS;
if PROGRAM =' ' and lag(STUDENTID)=STUDENTID then PROGRAM =PREV_PROGRAM;
if DIVISION=' ' and lag(STUDENTID)=STUDENTID then DIVISION=PREV_DIVISION;
run;
Like this?
data WANT;
set HAVE;
if STATUS ^=' ' then PREV_STATUS =STATUS ;
if PROGRAM ^=' ' then PREV_PROGRAM =PROGRAM ;
if DIVISION^=' ' then PREV_DIVISION=DIVISION;
retain PREV: ;
if STATUS =' ' and lag(STUDENTID)=STUDENTID then STATUS =PREV_STATUS;
if PROGRAM =' ' and lag(STUDENTID)=STUDENTID then PROGRAM =PREV_PROGRAM;
if DIVISION=' ' and lag(STUDENTID)=STUDENTID then DIVISION=PREV_DIVISION;
run;
Hi Chris....Yes this was very helpful...thanks
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.