AdministratorFebruary 7, 2021 at 3:48 pm
Method A: Retain + Merge
filename Alice 'file path\alice.xls';
proc import datafile=Alice out=alice(rename=(Name_ = Name)) dbms=Excel replace;
*************** Condition: By default, the blank in Name means it has same value as the previous obs. ************;
length Pre $20 New_Name $20 ;
retain Pre ' ';
if Name =" " then New_Name=Pre;
data First_Name ;
if New_Name = Name;
proc sort data=First_Name; by name; run;
proc sort data=B(keep=New_Name Q5) out=Full ;
length name $20;
merge First_Name full(rename=(new_name=name)) ;
AdministratorFebruary 7, 2021 at 3:53 pm
Use SUM statement to create a counter variable CNT, then split the dataset into 2 parts based on CNT=0 or not. Then merge them back together. Drop and rename vars if needed. See below:
length CNT 3 New $20 ;
retain New ' ' ;
if Name =' ' then CNT +1;
if CNT=0 then New=Name;
proc sort data=Old; by New; run;
proc sort data=W(keep= New Name Q5) out= New;
merge old new;
Method 3: Use By—NotSorted option.
length New $10 Pre $10;
by Name notsorted;
retain Pre 'AAA';
if flag=2 and not missing(Name) then New=Name;
if F=1 and not missing(Name) then output Old;
Then merge the OLD with B by New.
Some people may not have SAS in their company. If so, can you only use SQL to do it? It will be much more challenging in coding. But no pain, no gain!
- This reply was modified 7 months, 1 week ago by Justin.
MemberFebruary 7, 2021 at 3:39 pm
Q: Given the below dataset, for the Name variable, we want to impute the missing values with previous value. By default, they are same as the previous names.
name state date q1 q2 q3 q4
Jason NY 04-15-2010 very poor not satisfied yes no
Mike NY 6/12/2010 very good satisfied no yes
9/21/2010 very good satisfied no yes
3/8/2011 very good satisfied no yes
Mary CA 5-26-2010 good satisfied no yes
8/12/2009 good satisfied no yes
6/12/2012 very good not satisfied yes no
7/30/2003 bad satisfied no no
3/12/2008 good satisfied yes yes
Peter CA 12/3/2010 poor not satisfied yes no
12/11/2003 good satisfied yes no
10/23/2007 bad not satisfied no no
Log in to reply.