Read Massey raw data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

Read Massey raw data

Hi,

This is the data which I want to read it / I have large text file of this data . . .

can any one tell me how to read this type of Massey data ??

datalines;

STAT,"CSH","R",1400,"",200,10/01/2012,0.00,"767754BQ6",0,1067655,1,"MEL",09/30/2012,10/31/2012,11/05/2012,"this is the file which NOTE 9 3/8% Due 12/15/2015 JD15","YXZ ABC ORGNZTN CUSIP=767754BQ6",2,"252050",0.00,326910.94,"172010",326910.94,0.00,"USD"

STAT,"CSH","R",1400,"",200,10/01/2012,0.00,"767754BQ6",0,1067656,1,"MEL",09/30/2012,10/31/2012,11/05/2012,"society of ishvarkrupa and NOTE 9 3/8% Due 12/15/2015 JD15","PAID AS A RESULT OF A CUSIP=767754BQ6",2,"252050",0.00,9450.00,"172010",9450.00,0.00,"USD"

STAT,"CSH","R",1400,"",200,10/01/2012,0.00,"12543DAF7",0,1090002,1,"MEL",09/30/2012,10/31/2012,11/05/2012,"accomodation and the being human facility NOTE 8 7/8% Due 7/15/2015 JJ15","ABC/MMUNITY HEALTH CUSIP=12543DAF7",2,"252050",0.00,412763.23,"172010",412763.23,0.00,"USD"

STAT,"CSH","R",1400,"",200,10/01/2012,0.00,"12543DAF7",0,1090003,1,"MEL",09/30/2012,10/31/2012,11/05/2012,"INPUT OUTPUT OUTPUT INPUT FILE NOTE 8 7/8% Due 7/15/2015 JJ15","PAID AS A RESULT OF A CUSIP=12543DAF7",2,"252050",0.00,8200.00,"172010",8200.00,0.00,"USD"

STAT,"CSH","R",1400,"",203,10/01/2012,0.00,"46625YFJ7",0,1110165,1,"MEL",09/30/2012,10/31/2012,11/05/2012,"xy abdgc kfjeii iroei kfjfkdjf 2004-C3 G Adj % Due 1/15/2042 Mo-1","v f fdfd chndd fdfML CUSIP=46625YFJ7",2,"252050",0.00,22864.21,"172010",22864.21,0.00,"USD"

Here I have inserted 5 lines of data.. it is separated by , and "

I want 26 variables and I want to extract 9 digit code after CUSIP. . . its really massey I think its not possible.. can pls check it ???

Thank you -

Ranny


Accepted Solutions
Solution
‎12-03-2012 06:08 PM
Super User
Posts: 19,862

Re: Read Massey raw data

Not a sir...here's the code SAS outputted to my log and a sample afterwards of how to get the CUSPID

data WORK.TEST                                ;

%let _EFIERR_ = 0; /* set the ERROR detection macro variable */

infile '...\sample.txt' delimiter = ',' MISSOVER DSD lrecl=32767 ;

informat VAR1 $4. ;

informat VAR2 $5. ;

informat VAR3 $3. ;

informat VAR4 best32. ;

informat VAR5 $2. ;

informat VAR6 best32. ;

informat VAR7 mmddyy10. ;

informat VAR8 best32. ;

informat VAR9 $11. ;

informat VAR10 best32. ;

informat VAR11 best32. ;

informat VAR12 best32. ;

informat VAR13 $5. ;

informat VAR14 mmddyy10. ;

informat VAR15 mmddyy10. ;

informat VAR16 mmddyy10. ;

informat VAR17 $80. ;

informat VAR18 $41. ;

informat VAR19 best32. ;

informat VAR20 $8. ;

informat VAR21 best32. ;

informat VAR22 best32. ;

informat VAR23 $8. ;

informat VAR24 best32. ;

informat VAR25 best32. ;

informat VAR26 $5. ;

format VAR1 $4. ;

format VAR2 $5. ;

format VAR3 $3. ;

format VAR4 best12. ;

format VAR5 $2. ;

format VAR6 best12. ;

format VAR7 mmddyy10. ;

format VAR8 best12. ;

format VAR9 $11. ;

format VAR10 best12. ;

format VAR11 best12. ;

format VAR12 best12. ;

format VAR13 $5. ;

format VAR14 mmddyy10. ;

format VAR15 mmddyy10. ;

format VAR16 mmddyy10. ;

format VAR17 $80. ;

format VAR18 $41. ;

format VAR19 best12. ;

format VAR20 $8. ;

format VAR21 best12. ;

format VAR22 best12. ;

format VAR23 $8. ;

format VAR24 best12. ;

format VAR25 best12. ;

format VAR26 $5. ;

input

     VAR1 $
     VAR2 $
     VAR3 $
     VAR4
     VAR5 $
     VAR6
     VAR7
     VAR8
     VAR9 $
     VAR10
     VAR11
     VAR12
     VAR13 $
     VAR14
     VAR15
     VAR16
     VAR17 $
     VAR18 $
     VAR19
     VAR20 $
     VAR21
     VAR22
     VAR23 $
     VAR24
     VAR25
     VAR26 $

;

if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */

run;

data test2;

    set test;

    cuspid=reverse(substr(compress(reverse(var18)), 1, 9));

run;


View solution in original post


All Replies
Super User
Posts: 19,862

Re: Read Massey raw data

Look up the DSD option on input.

Quoted comma delimited files are one of the betters ones to have and less messy in my opinion, especially when you have a lot of long text fields.

Occasional Contributor
Posts: 19

Re: Read Massey raw data

Reeza,

Thank you for your opinion. but I am getting " and , in my dataset after using     lrecl =700 DSD TRUNCOVER;   option.

so I do not understand how to manage it and I also want to extract 9 digit code after CUSIP. . .

Thank you

Ranny

Super User
Posts: 19,862

Re: Read Massey raw data

I saved it as a txt file and has sas read it using proc import and CSV as file type, recl was 32767 and everything in your sample was read correctly with no quotation marks or comma's.

Missover was specified rather than truncover.

If you want the 9 digits after cusip then you need to parse that variable:

You can use a combination of the reverse and substr function if its always the last 9 digits.

Occasional Contributor
Posts: 19

Re: Read Massey raw data

Reeza,

Thank you sir !! can you please post the code for it so I can find my mistake.. ?? and do you think you think you can add one example also for how to extract the last 9 digit code after CUSIP ??

Really appreciate it . .

- Ranny

Solution
‎12-03-2012 06:08 PM
Super User
Posts: 19,862

Re: Read Massey raw data

Not a sir...here's the code SAS outputted to my log and a sample afterwards of how to get the CUSPID

data WORK.TEST                                ;

%let _EFIERR_ = 0; /* set the ERROR detection macro variable */

infile '...\sample.txt' delimiter = ',' MISSOVER DSD lrecl=32767 ;

informat VAR1 $4. ;

informat VAR2 $5. ;

informat VAR3 $3. ;

informat VAR4 best32. ;

informat VAR5 $2. ;

informat VAR6 best32. ;

informat VAR7 mmddyy10. ;

informat VAR8 best32. ;

informat VAR9 $11. ;

informat VAR10 best32. ;

informat VAR11 best32. ;

informat VAR12 best32. ;

informat VAR13 $5. ;

informat VAR14 mmddyy10. ;

informat VAR15 mmddyy10. ;

informat VAR16 mmddyy10. ;

informat VAR17 $80. ;

informat VAR18 $41. ;

informat VAR19 best32. ;

informat VAR20 $8. ;

informat VAR21 best32. ;

informat VAR22 best32. ;

informat VAR23 $8. ;

informat VAR24 best32. ;

informat VAR25 best32. ;

informat VAR26 $5. ;

format VAR1 $4. ;

format VAR2 $5. ;

format VAR3 $3. ;

format VAR4 best12. ;

format VAR5 $2. ;

format VAR6 best12. ;

format VAR7 mmddyy10. ;

format VAR8 best12. ;

format VAR9 $11. ;

format VAR10 best12. ;

format VAR11 best12. ;

format VAR12 best12. ;

format VAR13 $5. ;

format VAR14 mmddyy10. ;

format VAR15 mmddyy10. ;

format VAR16 mmddyy10. ;

format VAR17 $80. ;

format VAR18 $41. ;

format VAR19 best12. ;

format VAR20 $8. ;

format VAR21 best12. ;

format VAR22 best12. ;

format VAR23 $8. ;

format VAR24 best12. ;

format VAR25 best12. ;

format VAR26 $5. ;

input

     VAR1 $
     VAR2 $
     VAR3 $
     VAR4
     VAR5 $
     VAR6
     VAR7
     VAR8
     VAR9 $
     VAR10
     VAR11
     VAR12
     VAR13 $
     VAR14
     VAR15
     VAR16
     VAR17 $
     VAR18 $
     VAR19
     VAR20 $
     VAR21
     VAR22
     VAR23 $
     VAR24
     VAR25
     VAR26 $

;

if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */

run;

data test2;

    set test;

    cuspid=reverse(substr(compress(reverse(var18)), 1, 9));

run;


Occasional Contributor
Posts: 19

Re: Read Massey raw data

Reeza,

Thank you sir !! It was really helpful for me to understand the whole concept of it.

I am thinking that you are an expert, well I am just learning SAS now a days, so your suggestion will brings new ideas to me.

Thank you again and which book do you prefer to solve this type of issues ??

Regards,

Ranny !!

Respected Advisor
Posts: 3,156

Re: Read Massey raw data

Fareeza's solution will really save you a lot effort down the road with the cost of some overhead. Well, if you are as lazy as me, and just want to read it into SAS, you can pretend they are all 'character':

data have;

infile cards dsd truncover;

informat v1-v26 $100.;

input v1-v26;

cuspid=scan(v18,-1,'=');

cards;

STAT,"CSH","R",1400,"",200,10/01/2012,0.00,"767754BQ6",0,1067655,1,"MEL",09/30/2012,10/31/2012,11/05/2012,"this is the file which NOTE 9 3/8% Due 12/15/2015 JD15","YXZ ABC ORGNZTN CUSIP=767754BQ6",2,"252050",0.00,326910.94,"172010",326910.94,0.00,"USD"

STAT,"CSH","R",1400,"",200,10/01/2012,0.00,"767754BQ6",0,1067656,1,"MEL",09/30/2012,10/31/2012,11/05/2012,"society of ishvarkrupa and NOTE 9 3/8% Due 12/15/2015 JD15","PAID AS A RESULT OF A CUSIP=767754BQ6",2,"252050",0.00,9450.00,"172010",9450.00,0.00,"USD"

STAT,"CSH","R",1400,"",200,10/01/2012,0.00,"12543DAF7",0,1090002,1,"MEL",09/30/2012,10/31/2012,11/05/2012,"accomodation and the being human facility NOTE 8 7/8% Due 7/15/2015 JJ15","ABC/MMUNITY HEALTH CUSIP=12543DAF7",2,"252050",0.00,412763.23,"172010",412763.23,0.00,"USD"

STAT,"CSH","R",1400,"",200,10/01/2012,0.00,"12543DAF7",0,1090003,1,"MEL",09/30/2012,10/31/2012,11/05/2012,"INPUT OUTPUT OUTPUT INPUT FILE NOTE 8 7/8% Due 7/15/2015 JJ15","PAID AS A RESULT OF A CUSIP=12543DAF7",2,"252050",0.00,8200.00,"172010",8200.00,0.00,"USD"

STAT,"CSH","R",1400,"",203,10/01/2012,0.00,"46625YFJ7",0,1110165,1,"MEL",09/30/2012,10/31/2012,11/05/2012,"xy abdgc kfjeii iroei kfjfkdjf 2004-C3 G Adj % Due 1/15/2042 Mo-1","v f fdfd chndd fdfML CUSIP=46625YFJ7",2,"252050",0.00,22864.21,"172010",22864.21,0.00,"USD"

;

Haikuo

Occasional Contributor
Posts: 19

Re: Read Massey raw data

Hai.kuo,

Your trick is pretty quick and very simple to learn and understand. It was helpful for me to understand shortcut I guess !! Smiley Happy

Thank you sir !!

- Ranny

Super User
Posts: 19,862

Re: Read Massey raw data

I always forget the scan and -1 option....

But I copied from the log, so not sure which is lazier overall :smileysilly:

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 369 views
  • 7 likes
  • 3 in conversation