topic Reg:DATA LOADING in SAS Programming

Reg:DATA LOADING

R_Win — Wed, 30 Nov 2011 13:14:40 GMT

Hi i am getting data in txt file with having Enter(not word it means going to next line)

i am having the data like this as i am having 72 variables and 80000 obs

i want the output

Name user    no
ajay    kumar    2656
muni   carrom 455676
ajay   kumar   9890
vijay   kumar   589
user   flow     568

Reg:DATA LOADING

art297 — Wed, 30 Nov 2011 13:51:42 GMT

One possibility would be to run one datastep to rewrite the file, and then another to read it. E.g.:

data _NULL_ ;

FILE "c:\art\want.txt" RECFM=N ;

infile cards;

input;

do i=1 to length(_infile_);

dummy=substr(_infile_,i,1);

IF dummy GT '13'X THEN do;

PUT dummy $CHAR1. ;

end;

cards;

ajay|ku

mar|2656|

muni|carr

om|455676|

;

data want;

infile "c:\art\want.txt" dlm="|" dsd;

informat first last $15.;

input first last number @@;

run;

Reg:DATA LOADING

FriedEgg — Wed, 30 Nov 2011 16:07:06 GMT

I will not say this works for all possible cases but it works with the examples given:

data foo;

length slurp $32000;

retain slurp '';

input @;

slurp=cats(of slurp _infile_);

if not mod(countw(slurp,'|'),3) then

do;

do i=1 to countw(slurp,'|') by 3;

name=input(scan(slurp,i,'|'),$32.);

user=input(scan(slurp,i+1,'|'),$32.);

no=input(scan(slurp,i+2,'|'),8.);

output;

end;

slurp='';

end;

drop slurp i;

cards;

ajay|ku

mar|2656

muni|carr

om|455676

;

run;

Reg:DATA LOADING

FriedEgg — Wed, 30 Nov 2011 16:11:05 GMT

R_Win - Can you please let me know what the actual hex value for the 'ENTER' is?

Re: Reg:DATA LOADING

R_Win — Thu, 01 Dec 2011 06:09:04 GMT

it is '13'X i am thinking as i searched in net it has given as..

Re: Reg:DATA LOADING

R_Win — Thu, 01 Dec 2011 06:31:29 GMT

data temp;
infile cards missover truncover;
input sclurp $32000. ;
cards;
ajay|ku
mar|2656|34

muni|carr
om|455676|56

ajay|kumar|9890|78|vijay|kumar|589|90|
user|flow|568

|67
run;

data temp2;
/*input @;*/
set temp;
sclurp=cats(of sclurp );
retain sclurp '';
if not mod(countw(sclurp,'|'),4) then
do;
   do i=1 to countw(sclurp,'|') by 4;
    name=input(scan(sclurp,i,'|'),$32.);
    user=input(scan(sclurp,i+1,'|'),$32.);
    no=input(scan(sclurp,i+2,'|'),8.);
    age=input(scan(sclurp,i+3,'|'),8.);
    output;
   end;
   sclurp='';
end;
drop sclurp i;

run;
proc print;
run;

I am trying by this way i am not getting the output i wnat in this way i want the inital data in to a dataset as i cant give datalines as my data is huge

Re: Reg:DATA LOADING

R_Win — Thu, 01 Dec 2011 07:36:45 GMT

data temp;
infile cards missover truncover;
input sclurp $32000. ;
cards;
ajay|ku
mar|2656|34

muni|carr
om|455676|56

ajay|kumar|9890|78|vijay|kumar|589|90|
user|flow|568

|67
run;

run;
proc print;
run;

Tryed by this way it was getting errors

Reg:DATA LOADING

Ksharp — Thu, 01 Dec 2011 08:20:10 GMT

How about:

data want(keep=name user no);
infile datalines eof=last;
length name user no temp $ 40;
input a $1. @@;
retain temp name user no;
if a ne '|' then temp=cats(temp,a);
 else do;
         count+1;
         select(mod(count,3));
          when (1) name=temp;
          when (2) user=temp;
          when (0) do; no=temp;output;end;
         end;
         call missing(temp);
       end;
return;
last:  no=temp;output;
datalines;
ajay|ku
mar|2656|
muni|carr
om|455676|         
ajay|kumar|9890|vijay|kumar|589|user|flow|568
;
run;

Ksharp

Reg:DATA LOADING

R_Win — Thu, 01 Dec 2011 09:24:52 GMT

length name user no temp $ 40;

data want;

input temp $300.;

cards;

ajay|ku

mar|2656|

muni|carr

om|455676|

589|user|flow|568

run;

data want1(keep=name user no);

set want;

length name user no temp $ 40;

input a $1. @@;

retain temp name user no;

if a ne '|' then temp=cats(temp,a);

else do;

count+1;

select(mod(count,3));

when (1) name=temp;

when (2) user=temp;

when (0) do; no=temp;output;end;

end;

call missing(temp);

end;

return;

last: no=temp;output;

datalines;

ajay|ku

mar|2656|

muni|carr

om|455676|

589|user|flow|568

;

run;

proc print;

run;

Actually this contains datalines reading from the same dataset actually i wnat to read the data intially to a dataset and from that dataset i want to read to another dataset as you have written this code by using datalines as my data is huge i cant keep the datalines...

Re: Reg:DATA LOADING

Ksharp — Thu, 01 Dec 2011 09:59:52 GMT

But you said you have a txt file ,that is almost like this situation.

data want(keep=name user no);
infile  'c:\x.txt' eof=last;
length name user no temp $ 40;
input a $1. @@;
retain temp name user no;
if a ne '|' then temp=cats(temp,a);
 else do;
         count+1;
         select(mod(count,3));
          when (1) name=temp;
          when (2) user=temp;
          when (0) do; no=temp;output;end;
         end;
         call missing(temp);
       end;
return;
last: no=temp;output; 
run;

Ksharp

Re: Reg:DATA LOADING

R_Win — Thu, 01 Dec 2011 12:40:56 GMT

i have taken the data like this

Data taken in txt file:

---------------------------

nani|kumar
|20

But i am getting wrong output:

----------------------------------------

Obs name user no

                                                 1     nani     kumar       20kumar
                                                 2     raju     45          arun
                                                 3     kumar    78naresh    k
                                                 4     kumar    78naresh    45

Desired output

----------------------------

nani kumar 20

kumar raju 45

arun kumar 78

naresh k 45

Re: Reg:DATA LOADING

art297 — Thu, 01 Dec 2011 13:39:49 GMT

In your examples you have shown two quite different situations. One has name1,name2 and number, and the other has name1,name2, number1 and number2. Obviously, you need different input statements for those two very different situations.

Did you try the method that I had initially proposed?

Re: Reg:DATA LOADING

art297 — Thu, 01 Dec 2011 13:58:27 GMT

You have added two extra conditions. Now, in addition to having an extra numeric field, sometime your records end with a pipe, and sometimes they don't.

I tested the following with your new set of test data:

data _NULL_ ;

FILE "c:\want.txt" RECFM=N ;

infile cards;

input;

do i=1 to length(_infile_);

dummy=substr(_infile_,i,1);

if dummy eq "|" then counter+1;

if counter eq 3 and anyalpha(dummy) then do;

put "|";

counter+1;

end;

if counter eq 4 then counter=0;

IF dummy GT '13'X THEN do;

PUT dummy $CHAR1. ;

end;

cards;

ajay|ku

mar|2656|34

muni|carr

om|455676|56

ajay|kumar|9890|78|vijay|kumar|589|90|

user|flow|568

|67

;

data want;

infile "c:\want.txt" dlm="|" dsd;

informat first last $15.;

input first last number1 number2 @@;

run;

Re: Reg:DATA LOADING

R_Win — Thu, 01 Dec 2011 14:01:30 GMT

yes i have tried but not working

data foo;

length slurp $32000;

retain slurp '';

input @;

slurp=cats(of slurp _infile_);

if not mod(countw(slurp,'|'),3) then

do;

do i=1 to countw(slurp,'|') by 3;

name=input(scan(slurp,i,'|'),$32.);

user=input(scan(slurp,i+1,'|'),$32.);

no=input(scan(slurp,i+2,'|'),8.);

output;

end;

slurp='';

end;

drop slurp i;

cards;

ajay|ku

mar|2656

muni|carr

om|455676

;

run;

This is ok but i wnat it with outcards to insert as i am having 70000 obs i cant insert ,can u create dataset fisrt and after that we can use the code

data temp;
infile cards missover truncover;
input sclurp $32000. ;
cards;
ajay|ku
mar|2656

muni|carr
om|455676

user|flow|568

run;

data temp2;
/*input @;*/
set temp;
sclurp=cats(of sclurp );
retain sclurp '';
if not mod(countw(sclurp,'|'),4) then
do;
   do i=1 to countw(sclurp,'|') by 4;
    name=input(scan(sclurp,i,'|'),$32.);
    user=input(scan(sclurp,i+1,'|'),$32.);
    no=input(scan(sclurp,i+2,'|'),8.);
        output;
   end;
   sclurp='';
end;
drop sclurp i;

run;
proc print;
run;

as mentioned below i am trying by this way but i am not getting the output as getting ERROR as cards or infile is no there art can u help me in this.

Thqs in advance

Re: Reg:DATA LOADING

art297 — Thu, 01 Dec 2011 14:35:57 GMT

Same difference, but the code has to be changed if you really only have name,name,number. The following was written to account for the data you provided that had 2 names and 2 numbers in each record:

data _NULL_ ;

FILE "c:want.txt" RECFM=N ;

infile "c:\mydata.txt";

input;

do i=1 to length(_infile_);

dummy=substr(_infile_,i,1);

if dummy eq "|" then counter+1;

if counter eq 3 and anyalpha(dummy) then do;

put "|";

counter+1;

end;

if counter eq 4 then counter=0;

IF dummy GT '13'X THEN do;

PUT dummy $CHAR1. ;

end;

run;

data want;

infile "c:\want.txt" dlm="|" dsd;

informat first last $15.;

input first last number1 number2 @@;

run;

Reg:DATA LOADING

FriedEgg — Thu, 01 Dec 2011 17:28:56 GMT

Did you get this from searching the internet or from actually looking at the file? Does the file contain any proper EOL chars?

What OS are you running this job on?

Reg:DATA LOADING

FriedEgg — Thu, 01 Dec 2011 17:32:12 GMT

Looking at the above very quickly I see at least one issue.

sclurp=cats(of sclurp );

this is not collecting like you expect.

Reg:DATA LOADING

Peter_C — Thu, 01 Dec 2011 17:51:10 GMT

Hi i am getting data in txt file with having Enter(not word it means going to next line)
i am having the data like this as i am having 72 variables and 80000 obs 

ajay|ku
mar|2656|
muni|carr
om|455676| 
ajay|kumar|9890|vijay|kumar|589|user|flow|568
i want the output
Name user no
ajay kumar 2656
muni carrom 455676
ajay kumar 9890
vijay kumar 589
user flow 568

surely that is simple

"enter" is the default end-of-line character SAS expects for simple (even delimited) input.

The step is just

data your_data( compres= yes ) ;

infile 'your_text_file' DSD dlm= '|' lrecl=8000 truncover ;

input ( col1-col72)( :$30. ) ;

run ;

Re: Reg:DATA LOADING

Tom — Thu, 01 Dec 2011 18:00:22 GMT

I would use something like this to convert to one row per observation so that it can be read using DSD option on an INFILE statement.

This program removes all of the CR/LF characters and insert a line break after every third vertical bar that it reads.

To use for your large file with 72 variables then change the 3 to a 72 in the MOD() function call.

Note that is is dependent on the vertical bars that I see on the end of the lines. If those are not there then the logic will need to be more complex.

data _null_;
  infile tmpfile1 recfm=f lrecl=1;
  file tmpfile2 lrecl=2000 ;
  input char $char1. ;
  if char in ('0d'x,'0a'x) then delete;
  if (char='|') then do;
    words+1;
    if 0=mod(words,3) then put ;
    else put char $char1. @;
  end;
  else put char $char1. @;
run;

Input file

ajay|ku
mar|2656|
muni|carr
om|455676|
ajay|kumar|9890|vijay|kumar|589|user|flow|568

Output file

ajay|kumar|2656
muni|carrom|455676
ajay|kumar|9890
vijay|kumar|589
user|flow|568

Re: Reg:DATA LOADING

Ksharp — Fri, 02 Dec 2011 03:23:16 GMT

OK.

data want(keep=name user no );
infile 'c:\x.txt' lrecl=2000 pad ;
length name user no temp $ 40 b $ 1;
input a $char1. @@;
retain temp name user no b;
if a ne '|' then temp=cats(temp,a);
if a='|' or (anydigit(b) and notdigit(a)) then do;
                                       count+1;
                                       select(mod(count,3));
                                         when (1) name=temp;
                                         when (2) user=temp;
                                         when (0) do; no=temp;output;end;
                                        end;
                                        call missing(temp);
                                        end;
b=a;
run;


















data want(keep=name user no );
infile datalines ;
length name user no temp $ 40 b $ 1;
input a $char1. @@;
retain temp name user no b;
if a ne '|' then temp=cats(temp,a);
if a='|' or (anydigit(b) and notdigit(a)) then do;
                                       count+1;
                                       select(mod(count,3));
                                         when (1) name=temp;
                                         when (2) user=temp;
                                         when (0) do; no=temp;output;end;
                                        end;
                                        call missing(temp);
                                        end;
b=a;
datalines;
ajay|ku
mar|2656|
muni|carr
om|455676|         
ajay|kumar|9890|vijay|kumar|589|user|flow|568
nani|kumar
    |20
kumar|raju|45|arun|kumar|78
n
aresh|k|45
;
run;

Ksharp