DATA Step, Macro, Functions and more

Reg:Comma and codes

Reply
Frequent Contributor
Posts: 140

Reg:Comma and codes

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.

Frequent Contributor
Posts: 140

Reg:Comma and codes

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"

Respected Advisor
Posts: 3,777

Reg:Comma and codes

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. 

Super User
Posts: 9,687

Reg:Comma and codes

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

Super User
Posts: 9,687

Reg:Comma and codes

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

Regular Contributor
Posts: 229

Re: Reg:Comma and codes

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;                                --

PROC Star
Posts: 7,363

Re: Reg:Comma and codes

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;

Regular Contributor
Posts: 229

Re: Reg:Comma and codes

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

Super User
Super User
Posts: 6,502

Re: Reg:Comma and codes

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.

Super User
Posts: 9,687

Re: Reg:Comma and codes

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

Regular Contributor
Posts: 229

Reg:Comma and codes

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

Frequent Contributor
Posts: 140

Re: Reg:Comma and codes

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

Attachment
Super User
Posts: 9,687

Re: Reg:Comma and codes

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

Frequent Contributor
Posts: 140

Reg:Comma and codes

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"

Super User
Posts: 9,687

Reg:Comma and codes

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

Ask a Question
Discussion stats
  • 22 replies
  • 359 views
  • 1 like
  • 6 in conversation