Reading Pipe delimited file with CVS extension

[karthigao]

Hi, I am trying to read this (attached) pipe delimited file into SAS but  proc import throw's an error like this:

4936 proc import
4936! datafile="C:\Users\test\VisitSummary.csv"
4937 out=IRT
4938 dbms=csv replace ;
4939
4940 DELIMITER='|';
4941 getnames=Yes;
4942 datarow=2;
4943 run;

Unable to sample external file, no data in first 5 records.
ERROR: Import unsuccessful. See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 3.54 seconds
cpu time 0.12 seconds

data step has no error but it always comes as 0 observation.

4944 data WORK.IRT_2 ;
4945 infile 'C:\Users\test\VisitSummary.csv'
4946 delimiter = '|' DSD  lrecl=32767 firstobs=2 missover;
4947
4948 input
4949
4950 TRIALID $
4951 COUNTRYID $
4952 Country $
4953 SITEID $
4954 Principal_Investigator $
4955 Location_Name $
4956 SUBJID $
4957 SUBJID2 $
4958 YOB $
4959 Current_Status On_Treatment $
4960 Active_Inactive $
4961 Part $
4962 Cohort $
4963 Dose_Regimen $
4964 Stratum $
4965 Treatment_Name $
4966 Visit_Name $
4967 Visit_Date $
4968 Treatment_Dose $
4969 Kit_Numbers_Assigned $
4970 Kit_Type_Description $
4971 Lot_Numbers_Assigned $
4972 Batch_Numbers_Assigned $
4973 Kit_Verification_Date $
4974 Username $
4975 ;
4976 run;

NOTE: The infile 'C:\Users\test\VisitSummary.csv' is:
Filename=C:\Users\test\VisitSummary.csv,
RECFM=V,LRECL=32767,File Size (bytes)=147679,
Last Modified=13Jul2023:10:01:54,
Create Time=29Nov2021:12:42:10

NOTE: 0 records were read from the infile
'C:\Users\test\VisitSummary.csv'.
NOTE: The data set WORK.IRT_2 has 0 observations and 26 variables.
NOTE: DATA statement used (Total process time):
real time 3.32 seconds
cpu time 0.01 seconds

Please help me read this file into SAS. I am not sure what mistake I am doing here. I have attached a copy of the file as well.

[Tom]

Why does the file have all of those commas at the end of the line?
Did you accidentally open the original CSV file in EXCEL and then re-save it?  If so can you get back to the original file before it was touched by EXCEL?

Even so the file works fine for me.  You just might have to add a step to clean up that last field.

filename csv "C:\downloads\pipedelimted with CSV extension.csv";

proc import file=csv dbms=csv out=test replace;
  delimiter='|';
run;

proc print width=min;
run;

Spoiler

                                                        Principal_
Obs    TRIALID      COUNTRYID    Country    SITEID     Investigator              Location_Name             SUBJID    SUBJID2

 1    GCT1046-04       ES         Spain     ES004     Fern, Miguel       Clinica Universidad de Navarra     1001      1001
 2    GCT1046-04       ES         Spain     ES004     Fero, Miguel       Clinica Universidad de Navarra     1002      1002
 3    GCT1046-04       ES         Spain     ES007     Ram, Inmaculada    Hospital Virgen de la Victoria     1004      1004

                                                        Active_                                               Dose_
Obs   YOB     Current_Status            On_Treatment    Inactive        Part         Cohort                  Regimen

 1    1969    Enrolled                       Y          Active      Safety Run-In    Safety Run-in: Arm C      Q6W
 2    1961    Screen Failure                 N          Inactive    Safety Run-In    n/a                       n/a
 3    1964    Treatment Discontinued         N          Inactive    Safety Run-In    Safety Run-in: Arm C      Q6W


Obs   Stratum    Treatment_Name                  Visit_Name                    Visit_Date     Treatment_Dose

 1      n/a      Treatment Arm C  fexo 400mg     Enrollment (Cycle 1 Day 1)    01-DEC-2021    100 mg,400 mg,400 mg,400 mg,400 mg
 2      n/a      n/a                             Screen Failure                03-DEC-2021    n/a
 3      n/a      Treatment Arm C - pEXO 400mg    Screening                     11-JAN-2022    n/a


Obs   Kit_Numbers_Assigned             Kit_Type_Description

 1    10203,30063,30064,30065,30066    xxxx02 100 mg/vial,Pex 100mg/vial,fexo 100mg/vial,teri 100mg/vial,Fetto 100mg/vial
 2    n/a                              n/a
 3    n/a                              n/a


Obs   Lot_Numbers_Assigned

 1    E222115-0004L001,E222115-0003L001,E222115-0003L001,E222115-0003L001,E222115-0003L001
 2    n/a
 3    n/a

                                                                                              Kit_Verification_
Obs   Batch_Numbers_Assigned                                                                        Date

 1    E222115-0004L001,E222115-0003L001,E222115-0003L001,E222115-0003L001,E222115-0003L001           n/a
 2    n/a                                                                                            n/a
 3    n/a                                                                                            n/a


Obs   Username______________________

 1    De la Guardia, Eduardo
 2    De la Guardia, Eduardo,,,,,,,,,,,,,,,,,,,,
 3    Fern YUI, Desireeh,,,,,,,,,,,,,,,,,,,,

Step to remove the trailing commas from USERNAME (and rename it also).

data fixed;
  set test ;
  length username $80 ;
  username=translate(trim(translate(trim(Username______________________),', ',' ,')),', ',' ,');
run;

proc print;
 var username: ;
run;

Obs    Username______________________                       username

 1     De la Guardia, Eduardo                        De la Guardia, Eduardo
 2     De la Guardia, Eduardo,,,,,,,,,,,,,,,,,,,,    De la Guardia, Eduardo
 3     Fern YUI, Desireeh,,,,,,,,,,,,,,,,,,,,        Fern YUI, Desireeh

[karthigao]

Yes, I did re-save it since I Dint want to post the entire files here.  Also when I opened the files both in excel and notepad I did not find any comma anywhere in the files. 

[Tom · Accepted solution]

Normally that is a symptom of a file created by EXCEL on a MAC.  It seems that Excel never got the memo that MacOS is now a flavor of UNIX and it generates text files with CR as the end of line string instead of the LF that is normally used on UNIX.

filename irt "C:\Users\test\VisitSummary.csv" termstr=cr;
proc import dbms=csv datafile=irt out=irt replace;
  delimiter='|';
run;

[karthigao]

This one work perfectly . Thank you very much.