the data in my output is going to the next line and the layout is all messed up. i tried different ways to remove the carraige returns but not successful. talking about columns - objectives and notes. could someone please help me?
Thank you!
CallID | CallType | CallDate | CallStatus | Objectives | Notes | SOFDocument | UserType | SDSRepID | EmployeeID | SalesRepLastName | SalesRepFirstName | SalesRepMiddleName |
15356 | Detail Only | ######## | Complete | Objectives: Axumin - Prostate NEXT STEPS: | Notes: Axumin - Prostate MESSAGES: Indication, Discussed: Yes, Customer Response: , Resources Utilized: , Fol. Up: No | SalesRep | 196 | 2879142 | Moody | Meredith | ||
15358 | Detail Only | ######## | Complete | Objectives: Axumin - Prostate NEXT STEPS: | Notes: Axumin - Prostate MESSAGES: Indication, Discussed: Yes, Customer Response: , Resources Utilized: , Fol. Up: No | SalesRep | 196 | 2879142 | Moody | Meredith |
See this thread:
@sruthim wrote:
the data in my output is going to the next line and the layout is all messed up. i tried different ways to remove the carraige returns but not successful. talking about columns - objectives and notes. could someone please help me?
Thank you!
CallID CallType CallDate CallStatus Objectives Notes SOFDocument UserType SDSRepID EmployeeID SalesRepLastName SalesRepFirstName SalesRepMiddleName 15356 Detail Only ######## Complete Objectives:
Axumin - Prostate NEXT STEPS:Notes:
Axumin - Prostate MESSAGES:
Indication, Discussed: Yes, Customer Response: , Resources Utilized: , Fol. Up: NoSalesRep 196 2879142 Moody Meredith 15358 Detail Only ######## Complete Objectives:
Axumin - Prostate NEXT STEPS:Notes:
Axumin - Prostate MESSAGES:
Indication, Discussed: Yes, Customer Response: , Resources Utilized: , Fol. Up: NoSalesRep 196 2879142 Moody Meredith
Thank you @Reeza
Can you show us what the CSV file actually looks like? You appear to have copied something from a spreadsheet program and pasted it into the forum because it ended up looking like an HTML table instead of lines of text.
Use the Insert Code icon (looks like {i} on the menu bar) to get a pop-up window where you can paste example lines from your CSV file. Make sure to open the CSV file using a text editor and not a spreadsheet so you can see the actual lines of text. If you cannot figure that out just ask SAS to show you want is in the file.
data _null_;
infile 'name of my csv file' obs=5;
input;
list;
run;
"CallID","CallType","CallDate","CallStatus","Objectives","Notes","SOFDocument","UserType","SDSRepID","EmployeeID","SalesRepLastName","SalesRepFirstName","SalesRepMiddleName","SalesRepFullName","SalesRepAddressType","SalesRepAddress1","SalesRepAddress2","SalesRepAddress3","SalesRepCity","SalesRepState","SalesRepPostalCode","SalesRepPhone","SalesRepPhoneExt","SalesRepFax","SalesRepEmail","TerritoryLevel1Description","TerritoryLevel2Description","TerritoryLevel3Description","TerritoryLevel4Description","TerritoryLevel5Description","TerritoryLevel6Description","TerritoryLevel1","TerritoryLevel2","TerritoryLevel3","TerritoryLevel4","TerritoryDesc","SDSTerritoryID","TerritoryCode","TerritoryType","SDSCallEntityID","SDSCustomerID","CustomerID1","CustomerID2","CustomerID3","CustomerID4","CustomerID5","CustomerLastName","CustomerFirstName","CustomerMiddleName","CustomerFullName","Level","SDSLevelID","CustomerDegree","CustomerSpecialty","MedExam","SDSAddressID","CustomerAddressID","Address1","Address2","Address3","City","State","PostalCode","Phone","PhoneExt","Fax","SDSProductID","SDSStrengthID","ClientProductID1","ClientProductID2","ClientProductID3","ClientProductID4","ClientProductID5","FullProductDescription","ProductDescription","StrengthDescription","ProductManufacturer","SDSProductLotID","LotNumber","ExpireDate","Qty","P1","P2","P3","P4","P5","P6","P7","P8","P9","P10","CreateDate","CallDataSource" "15356","Detail Only","7/23/2019 9:00:00 AM","Complete","Objectives: Axumin - Prostate NEXT STEPS: ","Notes: Axumin - Prostate MESSAGES: Indication, Discussed: Yes, Customer Response: , Resources Utilized: , Fol. Up: No ","","SalesRep","196","2879142","Moody","Meredith"," ","Moody, Meredith","Primary Address","1738 Haight St. #208","","","San Francisco","CA","94117","415-307-0814","","","M.Moody@blueearthdx.com","US (SalesForce)","WEST","Northern California","","","","1026","1018","1022","","Northern California","1022","WE01004","Territory","16125","122867","1194883769","","","","","LEE","CHI","KEUNG","LEE, CHI KEUNG","Unassigned Decile","11","MD","","","41787","","2999 REGENT ST","#612","","BERKELEY","CA","94705","","","","","","","","","","","","","","","","","","","Axumin - Prostate","","","","","","","","","","7/25/2019 10:39:14 PM","App" "15358","Detail Only","7/23/2019 9:30:00 AM","Complete","Objectives: Axumin - Prostate NEXT STEPS: ","Notes: Axumin - Prostate MESSAGES: Indication, Discussed: Yes, Customer Response: , Resources Utilized: , Fol. Up: No ","","SalesRep","196","2879142","Moody","Meredith"," ","Moody, Meredith","Primary Address","1738 Haight St. #208","","","San Francisco","CA","94117","415-307-0814","","","M.Moody@blueearthdx.com","US (SalesForce)","WEST","Northern California","","","","1026","1018","1022","","Northern California","1022","WE01004","Territory","16127","122869","1184910044","","","","","OBERLIN","DANIEL","THOMAS","OBERLIN, DANIEL THOMAS","Unassigned Decile","11","MD","","","41789","","1441 EASTLAKE AVE STE 7416","","","LOS ANGELES","CA","90089","","","","","","","","","","","","","","","","","","","Axumin - Prostate","","","","","","","","","","7/25/2019 10:42:26 PM","App" "15546","Detail Only","7/23/2019 9:30:00 AM","Complete","Objectives:
Hi Tom,
i opened it with Notepad and here it is.
i am sorry i didnt get you. waht previously linked question?
@Reeza already provided you a link to another question about this same issue on this forum that has the solution.
This is the code i am using. %let date = 20190731; %let filename = Calls_X8668_20190731_cr; %let filepath = "C:\Data\Sruthi\Synergistix\&filename..csv"; DATA &filename; INFILE &filepath lrecl=10000 DLM = ',' DSD truncover firstobs = 2 TERMSTR=CRLF; INPUT CallID :$60. CallType:$60. CallDate:$60. CallStatus:$60. Objectives:$600. CreateDate:$60. CallDataSource:$60.; /*Objectives=tranwrd(Objectives,'0D0A'x, ''); notes=tranwrd(notes,'0D0A'x, '');*/ run;
okay. looking into it. was hoping to see an easier approach. but will try that. thank you!
@sruthim wrote:
okay. looking into it. was hoping to see an easier approach. but will try that. thank you!
Did you try the method in the other post? Did it work?
If not
If not
The logic of this step seems be to throw away everything except the embedded CR and LF's.
data _null_;
infile &dsnnme recfm=n sharebuffers;
file &dsnnme recfm=n;
retain open 0;
input
Objectives $char1.;
if Objectives = '"' then open = ^(open);
if open then do;
if Objectives = '0D'x then put &repD;
else if Objectives = '0A'x then put &repA;
end;
run;
Did you mean to do something like this instead?
data _null_;
infile &dsnnme recfm=n sharebuffers;
file &dsnnme recfm=n;
retain open 0;
input Objectives $char1.;
if Objectives = '"' then open = ^(open);
if open then do;
if Objectives = '0D'x then put &repD;
else if Objectives = '0A'x then put &repA;
else put Objectives $char1. ;
end;
else put Objectives $char1.;
run;
Here is an example. Good Luck.
options compress=yes;
DATA have;
INFILE 'c:\temp\x.txt' lrecl=10000 DSD truncover firstobs = 2 TERMSTR=CRLF length=len;
input x $varying10000. len;
if not missing(x);
run;
data temp;
set have;
if prxmatch('/^"\d{5,}"/',x) then group+1;
run;
data temp1;
length have $ 10000;
do until(last.group);
set temp;
by group;
have=cats(have,x);
end;
drop x;
run;
data want;
set temp1;
array x{*} $ 400 CallID CallType CallDate CallStatus Objectives Notes SOFDocument UserType;
do i=1 to dim(x);
x{i}=dequote(scan(have,i,',','qm'));
end;
drop i;
run;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.