BookmarkSubscribeRSS Feed
sas_Forum
Calcite | Level 5

I am having delimeter as ",  i was not able to load the data

data l;

infile cards dlm='",';

length a b c d e f g h i j $25.;

input a$ b$ c$ d$ e$ f$ g$ h$ i$ j$  ;

cards;

"acv","1000036513","","Te_ADDR","507 Main, PLAZA, BUDH MARG","","HYd","","","IN"

run;

For ex 507 Main, PLAZA, BUDH MARG is having comma between the obs i am getting the error lost card

NOTE: LOST CARD.

RULE:      ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----

9----+----0                    

680        run;

a=acv b=1000036513 c=Te_ADDR d=507 Main e=PLAZA f=BUDH MARG g=HYd h=IN i=  j=  _ERROR_=1 _N_=1

How can i load this what is the delimeter shold i keep.

22 REPLIES 22
sas_Forum
Calcite | Level 5

And the other problem is data is coming in 2 lines some times and coming in single line how can i do this..

Ex data:

"acv","1000036513","","Te_ADDR","507 Main, PLAZA, BUDH MARG","","HYd","","","IN"

"acv","1000036513","","Te_ADDR",
"507 Main, PLAZA, BUDH MARG","","HYd","","","IN"

"acv","1000036513","","Te_ADDR","507 Main, PLAZA, BUDH MARG","","HYd","","","IN"

"acv","1000

036513","","Te_ADDR","507 Main, PLAZA, BUDH MARG","","HYd","","","IN"

data_null__
Jade | Level 19

This may be caused by ALT<ENTER> being used in EXCEL.  Assuming of course that EXCEL was used to create the file.  You may be able to fix it with INFILE option TERMSTR=CRLF. 

Ksharp
Super User

You need to add dsd option.

data l;
infile cards  dsd;
format a b c d e f g h i j $25.;
input a$ b$ c$ d$ e$ f$ g$ h$ i$ j$  ;
cards;
"acv","1000036513","","Te_ADDR","507 Main, PLAZA, BUDH MARG","","HYd","","","IN"
;
run; 


Ksharp

Ksharp
Super User
filename x 'c:\x.txt';
data l;
file x;
length row $ 400;
retain row;
input;
row=cats(row,_infile_);
if countc(row,'"') ge 20 then do;put row; call missing(row);end;
cards;
"acv","1000036513","","Te_ADDR","507 Main, PLAZA, BUDH MARG","","HYd","","","IN"
"acv","1000036513","","Te_ADDR",
"507 Main, PLAZA, BUDH MARG","","HYd","","","IN"
"acv","1000036513","","Te_ADDR","507 Main, PLAZA, BUDH MARG","","HYd","","","IN"
"acv","1000
036513","","Te_ADDR","507 Main, PLAZA, BUDH MARG","","HYd","","","IN"
;
run; 
data want;
infile x dsd;
format a b c d e f g h i j $25.;
input a$ b$ c$ d$ e$ f$ g$ h$ i$ j$  ;
run;

Ksharp

R_Win
Calcite | Level 5

Hi ksharp i have tryed the code with infile but i am not getting the desired output i should get only 4 rows as enter is there as obs are coming in another rows also can u do the same by using infile statement with out using the cards as in the above example you have used cards and again pasted the observations can u do the same by infile statement please. data l;                                                                                -- infile 'C:\tt.txt'  dsd; length row $ 400;                                                                  - retain row; input;                                                                                                                        - row=cats(row,_infile_);                                                                                                                - if countc(row,'"') ge 20 then do;                                                                                                                                            - put row; call missing(row);                                                                                                                                                            - end; run;                                --

art297
Opal | Level 21

There are two questions I have to ask:

1. are you and sas_forum the same person?

and

2. why don't you ever (from what I have seen) credit people for providing you with correct or helpful answers?

I have seen FriedEgg and KSharp and Tom spend quite a bit of time addressing your questions, but I can't recall ever seeing you giving them points for doing so.  I'm sure they would appreciate the gesture.

As for your question, I just ran the following code, which was simply writing the data to a text file and then applying KSharp's code.  It seems to have worked for me:

filename y 'c:\original.txt';

filename x 'c:\x.txt';

data _null_;

  file y;

  input;

  put _infile_;

cards;

"acv","1000036513","","Te_ADDR","507 Main, PLAZA, BUDH MARG","","HYd","","","IN"

"acv","1000036513","","Te_ADDR",

"507 Main, PLAZA, BUDH MARG","","HYd","","","IN"

"acv","1000036513","","Te_ADDR","507 Main, PLAZA, BUDH MARG","","HYd","","","IN"

"acv","1000

036513","","Te_ADDR","507 Main, PLAZA, BUDH MARG","","HYd","","","IN"

;

data l;

  file x;

  infile y;

  length row $ 400;

  retain row;

  input;

  row=cats(row,_infile_);

  if countc(row,'"') ge 20 then do;

    put row;

    call missing(row);

  end;

run;

data want;

  infile x dsd;

  format a b c d e f g h i j $25.;

  input a$ b$ c$ d$ e$ f$ g$ h$ i$ j$  ;

run;

R_Win
Calcite | Level 5

can u do the same code by using infile (the data file path ) with out using the cards and pasting the data

Tom
Super User Tom
Super User

If you post an example file as an attachment then others can try reading it from an external file rather than as inline data.  When you just post the contents in the editor window you have probably already introduced changes that will make it hard for others to replicate the issues you are seeing.

Ksharp
Super User

OK.

Actually Art has already gave your answer.

NOTE: c:\have.txt is the file holding your origin data.

filename x 'c:\x.txt';
data _null_;
file x;
length row $ 400;
retain row;
infile 'c:\have.txt';
input;
row=cats(row,_infile_);
if countc(row,'"') ge 20 then do;put row; call missing(row);end;
run; 
data want;
infile x dsd;
informat a b c d e f g h i j $25.;
input a$ b$ c$ d$ e$ f$ g$ h$ i$ j$  ;
run;


Ksharp

R_Win
Calcite | Level 5

Thqs Ksharp,Art ,Tom for your Good and Quick Responce to me query

sas_Forum
Calcite | Level 5

data _null_;

length row $ 400;

retain row;

infile 'c:\comma_data.txt';

input;

row=cats(row,_infile_);

if countc(row,'"') ge 20 then do;put row; call missing(row);end;

run;

HI ksharp one Help i am having data with embeded in between " actually i am having 4 obs in 6 rows i have used the code above but not working.I want the data to read by using infile only actually there are 20 obs can you help me

Ksharp
Super User

OK. This is what you need?

filename x 'c:\temp.txt';

data _null_;

file x;

length row $ 400;

retain row;

infile 'c:\comma_data.txt';

input;

row=cats(row,_infile_);

if countc(row,'"') ge 20 then do;put row; call missing(row);end;

run;

data want;

infile x dsd;

informat a b c d e f g h i j $200.;

input a$ b$ c$ d$ e$ f$ g$ h$ i$ j$  ;

run;

Ksharp

sas_Forum
Calcite | Level 5

Actually i am running in server so that i can not keep

filename x 'c:\temp.txt';

file x;

I can use only by infile " file path"

Ksharp
Super User

Then use a temporary file.

filename x temp;

data _null_;

file x;

length row $ 400;

retain row;

infile 'c:\comma_data.txt';

input;

row=cats(row,_infile_);

if countc(row,'"') ge 20 then do;put row; call missing(row);end;

run;

data want;

infile x dsd;

informat a b c d e f g h i j $200.;

input a$ b$ c$ d$ e$ f$ g$ h$ i$ j$  ;

run;

Ksharp

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 22 replies
  • 1455 views
  • 1 like
  • 6 in conversation