topic Re: Reading a value containing commas and special characters like ~,(,- etc in SAS Programming

Reading a value containing commas and special characters like ~,(,- etc

ShaheenRanalvi — Tue, 28 Aug 2012 07:44:49 GMT

Hi ,

I have 20 variables and these are comma separated as shown below:(it is a csv file and i am testing the code so i can't change it into an excel file)

"001","1942","Response Assessment","07/22/1980","Active","Yes","","07-22-1983","Bone marrow","","Abnormal","","90~93,XXX,-X,add(1)(q21)x2,add(3)(p21)x2,-5,del(5)(q13q33)x2,-13,add(13)(q22)x2,+17,iso(17)(q10)x2,add(21)(p11.1),add(21)(q22)x2,+2~3mar[cp3]/46,XX[2]","","Yes","","07-22-2012","Bone marrow","","Abnormal"

Question is How do i read the value in red.It is basically a karyotypic description.

code snapshot-

data b;

infile abc.csv dlm=',' MISSOVER dsd lrecl = 60000 firstobs = 2 ;

informat CSPI11 $50. ;

informat birthYear1 best32. ;

informat collectionPointLabel1 $50. ;

informat encounterDate1 mmddyy10. ;

informat activityStatus1 $1000. ;

informat Were_cytogenetics_performed81 $4000. ;

informat WCPnc best12.;

informat Date_cytogenetics_performed1 mmddyy10. ;

informat SOCM $4000. ;

informat SOCMnc best12.;

informat Cytogenetics_results1 $4000. ;

informat CRnc best12.;

informat CKD $150. ;

informat PUDOCIA1 $60.;

informat Was_FISH_performed81 $4000. ;

informat WFPnc best12.;

informat Date_FISH_performed1 mmddyy10. ;

informat Source_of_FISH_material1 $4000. ;

informat SFMnc best12.;

informat FISH_results1 $4000. ;

informat FRnc best12.;

informat SAAFISHR1 $50. ;

informat PUDOFISHIA1 $60.;

informat SIGNATUREANDDATE_NOPCF1 $50. ;

informat SIGNATUREANDDATE_DFC1 mmddyy10. ;

informat SIGNATUREANDDATE_CCF1 $100. ;

format CSPI11 $50. ;

format birthYear1 best32. ;

format collectionPointLabel1 $50. ;

format encounterDate1 mmddyy10. ;

format activityStatus1 $1000. ;

format Were_cytogenetics_performed81 $4000. ;

format WCPnc best12.;

format Date_cytogenetics_performed1 mmddyy10. ;

format SOCM $4000. ;

format SOCMnc best12.;

format Cytogenetics_results1 $4000. ;

format CRnc best12.;

format CKD $150. ;

format PUDOCIA1 $60.;

format Was_FISH_performed81 $4000. ;

format WFPnc best12.;

format Date_FISH_performed1 mmddyy10. ;

format Source_of_FISH_material1 $4000. ;

format SFMnc best12.;

format FISH_results1 $4000. ;

format FRnc best12.;

format SAAFISHR1 $50. ;

format PUDOFISHIA1 $60.;

format SIGNATUREANDDATE_NOPCF1 $50. ;

format SIGNATUREANDDATE_DFC1 mmddyy10. ;

format SIGNATUREANDDATE_CCF1 $100. ;

input

CSPI11 $

birthYear1

collectionPointLabel1 $

encounterDate1

activityStatus1 $

Were_cytogenetics_performed81 $

WCPnc

Date_cytogenetics_performed1

SOCM $

SOCMnc

Cytogenetics_results1 $

CRnc

CKD $

PUDOCIA1 $

Was_FISH_performed81 $

WFPnc

Date_FISH_performed1

Source_of_FISH_material1 $

SFMnc

FISH_results1 $

FRnc

SAAFISHR1 $

PUDOFISHIA1 $

SIGNATUREANDDATE_NOPCF1 $

SIGNATUREANDDATE_DFC1

SIGNATUREANDDATE_CCF1 $;

run;

Please Help.

Thanks & Regards,

Shaheen

Re: Reading a value containing commas and special characters like ~,(,- etc

Patrick — Tue, 28 Aug 2012 08:48:42 GMT

As the values are quoted I can't really see where the problem should be. Do you get an error when running your code? And if yes: What's the error?

Re: Reading a value containing commas and special characters like ~,(,- etc

ShaheenRanalvi — Tue, 28 Aug 2012 08:53:06 GMT

Error is invalid data.

I will upload the file in some minutes,may be then the problem will be clear.

Thanks

Re: Reading a value containing commas and special characters like ~,(,- etc

Patrick — Tue, 28 Aug 2012 08:59:24 GMT

And how do you know that it's caused by this string?

Suggest you add a few lines of your real data as an attachement here so we can run your code and see what's causing the error.

Re: Reading a value containing commas and special characters like ~,(,- etc

ShaheenRanalvi — Tue, 28 Aug 2012 09:22:54 GMT

Thanks a tonne for such a quick reply.

Actually there was a line break in one of the values which i noticed when i saw the value in notepad++ ,(details below)

85~112,XXX,-X,add(1)(q21)x2,add(3)(p21)x2,-5,del(5)(q13q33)x2,-13,add(13)(q22)x2,+17,iso(17)(q10)x2,add(21)(p1

1.1),add(21)(q22)x2,+5mar[cp4]/46,XX[9]

Now after correcting this ,the code is working fine.

Thanks & Regards,

Shaheen

Re: Reading a value containing commas and special characters like ~,(,- etc

Patrick — Tue, 28 Aug 2012 09:55:41 GMT

Oh yes, this lovely line breaks coming from Excel are in my list of favorites. Good that you've spotted it.