CODEWITHSUNDEEP
sas
konsteinuser
konsteinuser

Reputation: 25

Import CSV file with , within delimiter SAS

I have this csv dataset named Movie:

ID,Underage,Name,Rating,Year, Rank on IMDb ,
M1021,,Elanor, Melanor,12,1879,5
M1203,Yes,IT,12,1999,1,
M0081,,Cars 2,13,1999,2,
M1371,No,Kiminonawa,12,2017,3,
M3416,,Living in the past, fading future,13,2018,12

I would like to import Movie into SAS such that "Elanor, Melanor" is the Name instead of 'Elanor' being under Name while 'Melanor' being in Rating.

I tried the follow code:

FILENAME XX '....Movie.csv';

data movieYY (drop=DLM1at field2);
 infile XX dlm=',' firstobs=2 dsd;
 format ID $5. Underage $3. Name $50. Year 4. Rating $3. 'Rank on IMDb'n 2.;
 input @;
 
 DLM1at = find(_INFILE_, ',');
 length field2 $4;
 field2 = substr(_INFILE_, DLM1at + 1, 4);
 
 if lengthn(compress(field2, '1234567890')) ne 0 then do;
 
 _INFILE_ = substr(_INFILE_, 1, dlm1at - 1) || ' ' ||
 
 substr(_INFILE_, dlm1at + 1);
 end;
 input ID Underage Name Year Rating 'Rank on IMDb'n;
run;

May I know what should i do? I am still a beginner in SAS. Thank you!

Upvotes: 1

Views: 384

Answers (1)

Stu Sztukowski
Stu Sztukowski

Reputation: 12849

Add quotes to the name of each movie, or use another delimiter. Any data within a delimited file that also has the same delimiter must be in quotes. For example:

data foo;
    infile datalines dlm="," dsd;
    length id 8. name $25.;

    input id name$;

    datalines;
1, "Smith, John"
2, "Cage, Nicolas"
;
run;

Upvotes: 2

Related Questions