Clean Imported Data

[smalek]

Hi
I have a a raw data file when imported into SAS yields a data set with the following format:

P_Num Mnemonic Methd Subsec Code Cnt
91000 SC1RPT GEL
AB
RPT 1
AB
ABSC 1
10500 BT MAN
ABO&RH
ABOR 1
BKH 1

Desired output:
P_Num Mnemonic Methd Subsec Code Cnt
91000 SC1RPT GEL AB RPT 1
91000 SC1RPT GEL AB ABSC 1
10500 BT MAN ABO&RH ABOR 1
10500 BT MAN ABO&RH BKH 1


Any suggestions will be much appreciated?
Thanks

 

[klaz2002]

How does your raw data file look before SAS reads it. Can you use the LIST type of input?

Klaz

 

[smalek]

The raw data file looks exactly as I had outlined above. Even after importing it into SAS it does not change. I need to clean it up such that it matches my desired output.
S.

 

[klaz2002]

OK you need to use position INPUT and a RETAIN statement. Looking at your raw data I count 6 variables that you need. It also has the values in almost the same position.

here is how I would do it.

data Mytestdata(keep= P_Num Mnemonic Methd Subsec Code Cnt  );
retain p_Num_chng Mnemonic_chng Methd_chng Subsec_chng Code_chng;


input P_Num 1-5  Mnemonic $ 7-13   Methd  $ 14-28 Subsec $ 40-52       Code $ 53-59     Cnt  60-63;

if p_num = . then
   p_num= p_num_chng;
else 
  p_num_chng = p_num;

if trim(Mnemonic) = '' then
   Mnemonic = Mnemonic_chng;
   else
    Mnemonic_chng = Mnemonic;

if trim(Methd) = '' then
   Methd = Methd_chng;
   else
    Methd_chng = Methd;

if trim(Subsec) = '' then
   Subsec = Subsec_chng;
   else
    Subsec_chng = Subsec;

if trim(Code) = '' then
   Code = Code_chng;
   else
    Code_chng = Code;
if cnt = 1;  
datalines;
91000  SC1RPT        GEL    
                                           AB    
                                                     RPT   1
                                           AB     
                                                    ABSC   1
10500 BT           MAN
                                       ABO&RH      
                                                    ABOR   1
                                                    BKH    1
;
run;

Obviously, you wouldn't use the DATALINES statement as you would read the raw file in using an INFILE statement.

Hope this helps you,
Klaz