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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.