LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (May 2011, week 3)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Tue, 17 May 2011 15:27:27 -0400
Reply-To:     "Bian, Haikuo" <HBian@FLQIO.SDPS.ORG>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Bian, Haikuo" <HBian@FLQIO.SDPS.ORG>
Subject:      Re: Problem with comma delimiter while importing data into SAS
              (through Access)
Comments: To: Irin later <irinfigvam@YAHOO.COM>
In-Reply-To:  <774294.1586.qm@web39409.mail.mud.yahoo.com>
Content-Type: text/plain; charset="iso-8859-1"

Hi,

The following code may be useful when you directly read from csv file. Some concatenating is needed.

data want (drop=lab1 lab2); infile cards dsd; length memberid $ 20 rule $ 3 pot 8. en 8. product $ 20 lab1 $ 20 lab2 $ 20 lab $ 20 provid $ 20 cpt $ 20 ; input memberid $ rule $ pot en product $ lab1 $ lab2 $ provid $ cpt $ ; lab=catt(lab1,',',lab2); *concatenating here;

cards; 555555555A,552,1,0,MM MM2,11,680/80 mm Hg,00500550005555,3077F 555555555A,557,1,0,MM MM2,11,680/80 mm Hg,00500550005555,3077F 555555555A,597,1,0,MM MM2,11,680/80 mm Hg,00500550005555,3077F ; run;

proc print; run;

Regards, Haikuo -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Irin later Sent: Tuesday, May 17, 2011 2:46 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Problem with comma delimiter while importing data into SAS (through Access)

I got a problem with a pseudo delimiter while import data to SAS. When I import data from MS SQL Server 2005 to csv file and then to MS Access 2007 I found out that our DW datamart contains some dirty data like Blood Pressure VALUE = 11,680/80 mm Hg . I believe , however, that the real problem is not in a wrong data value but rather in comma within the value which is read like a delimiter while I import file into Access. It creates a total mess in Access and then in its turn in my SAS dataset. After import, my data is shifted to the right, starting right after "11".

Below is how my CSV file look like:

memberid='555555555A' rule='552' pot=1 en=0 product='MM MM2' lab='11,680/80 mm Hg' provid='005005550005555' cpt='3077F' claimno='CM10055555555A-5005555' date=2009-12-30 00:00:00.000

555555555A,552,1,0,MM MM2,11,680/80 mm Hg,00500550005555,3077F 555555555A,557,1,0,MM MM2,11,680/80 mm Hg,00500550005555,3077F 555555555A,597,1,0,MM MM2,11,680/80 mm Hg,00500550005555,3077F

(I just cut all lines at the end...hopefully it will be readable, bu t in reality it mess till the very end)

I got used to use Access as an intermediate tool in my import to SAS for data manipulation and analysis .... How can I overcome the issue? Woud it help if I would read data directly from CSV into SAS? How can I read it properly from csv then? Could you, please, give me a hand with a code?

I would really appreciate your help!

Thank you in advance, Irin ----------------------------------------- Email messages cannot be guaranteed to be secure or error-free as transmitted information can be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The Centers for Medicare & Medicaid Services therefore does not accept liability for any error or omissions in the contents of this message, which arise as a result of email transmission.

CONFIDENTIALITY NOTICE: This communication, including any attachments, may contain confidential information and is intended only for the individual or entity to which it is addressed. Any review, dissemination, or copying of this communication by anyone other than the intended recipient is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message.


Back to: Top of message | Previous page | Main SAS-L page