Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Clean Imported Data

Status
Not open for further replies.

smalek

Programmer
Jan 15, 2009
28
CA
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
 
How does your raw data file look before SAS reads it. Can you use the LIST type of input?

Klaz
 
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.
 
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.
Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top