topic Re: Reg:Data Loading Issue in SAS Programming

Reg:Data Loading Issue

R_Win — Fri, 02 Dec 2011 09:34:52 GMT

i am getting data like this

kumar|raj|67|23-a
ug-90

muni|kunmar|90|12-j
an-98

mani|raju|78
|12-feb-
78

raju|k|34|10-feb-92

output

Name surname age date

kumar   raj         67 23-aug-90
muni    kunmar 90 12-jan-98
mani    raju        78 12-feb-78

raju k 34 10-feb-92

Reg:Data Loading Issue

Ksharp — Fri, 02 Dec 2011 09:58:06 GMT

Oh.God. Why didn't you show us the true data structure?

data want(keep=name user no date );
infile 'c:\x.txt' lrecl=2000 pad ;
length name user no date 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 missing(a) and mod(count,4)=3) then do;
                                       count+1;
                                       select(mod(count,4));
                                         when (1) name=temp;
                                         when (2) user=temp;
                                         when (3) no=temp;
                                         when (0) do; date=temp;output;end;
                                        end;
                                        call missing(temp);
                                        end;
b=a;
run;

Ksharp

Reg:Data Loading Issue

R_Win — Fri, 02 Dec 2011 10:19:46 GMT

Ksharp is there any way to increase the performacne i have tryed it was working on sample data if on 80000 obs it may take huge time for 1000 records it has taken 100 secnds like that actually i am having 30 variables now

Re: Reg:Data Loading Issue

Ksharp — Fri, 02 Dec 2011 13:02:16 GMT

I think you need to post some true data to let me to see the actual data structure of yours.

The way to promote process is to decrease the value of lrecl=.

data want(keep=temp);
infile 'c:\x.txt' ;
length temp $ 400;
input a $char1. @@;
retain temp ; 
temp=cats(temp,a);
pid=prxparse('/\d{2}-\w{3}-\d{2}/');
if prxmatch(pid,scan(temp,-1,'|')) then do;
output;call missing(temp);end;
run;

Ksharp

Re: Reg:Data Loading Issue

R_Win — Fri, 02 Dec 2011 13:38:15 GMT

K sharp i am sending the data below actually i am having 21 variables like c1 --c21 and the data is coming like this,but i have tryed the code of your but it was removing the spaces in between the data and taking time and some variables are 4000 in long.

bewlo is the sample data

===========================

Is the time taken
^9865^^^^COOL^dealt was good
it is ok for us^56^ok^AEFG^ ^13-aug-19
99^ok for it ^this is not prosable ^ Ecoreco provides the fulsize reduction

^ Registration link opens on at 3.00 pm. Any registration attempts before the specified time will not be considered in the
^To illustrate, suppose that a very large file named “Master” contains a column named “Code”^^^^23-jAN-1
980

REQ1^REQ2^REQ3^REQ4^REQ5^REQ6^13-JUNE-19
80^REQ8^REQ9

^REQ10^REQ11^13-MAR-19
97^REQ13^REQ14^REQ15^WE ARE
OK ^REQ17 ^REQ18 ^REQ19 ^12-FEB-2
011^UP

REQ1^REQ2^REQ3^REQ4^REQ5^REQ6^13-JUNE-1980^REQ8^REQ9^REQ10^REQ11^13-MAR-1997^REQ13^REQ14^REQ15^WE AREOK ^REQ17 ^REQ18 ^REQ19 ^REQ20^12-FEB-2
011

==============

Reg:Data Loading Issue

Tom — Fri, 02 Dec 2011 13:48:38 GMT

If you click on the "Use advanced editor" link in the upper right of the input form you will get a page that will let you upload a file.

The problem with this format is finding the end of record.

Do you have any control over the generation of this file?

Are the extra line breaks caused by field values that contain CR or LF characters? One solution is to replace those characters in the source system with other characters so that they will not be in the data file.

Re: Reg:Data Loading Issue

R_Win — Fri, 02 Dec 2011 14:20:20 GMT

Hi you can find the data for that

Re: Reg:Data Loading Issue

Ksharp — Fri, 02 Dec 2011 14:35:15 GMT

Now what do you want output to look like?

Re: Reg:Data Loading Issue

R_Win — Fri, 02 Dec 2011 14:56:55 GMT

Actually i have uploaded the raw data as i have copy pasted earlier now i have given in that file

Re: Reg:Data Loading Issue

Ksharp — Fri, 02 Dec 2011 15:24:18 GMT

I know you have uploaded a attachment.

What I mean is what output you need.

Your data are very messy. I do not know From which column started to which column end for an obsersation.

Re: Reg:Data Loading Issue

art297 — Fri, 02 Dec 2011 15:35:12 GMT

The three records included in the attachment contain 1 records that only has 20 variables and two records that has 21 variables. Unlike your previous example "^" is used as the delimiter rather than "|".

In the first record the last (20th) variable was a date, in the second record the last (21st) variable contained the string "ok", and the last variable in the third record (21st) was a date.

Either you didn't send the actual data, the data is (like Ksharp suggested) too messed up to work with, and/or you haven't provided enough info so that anyone could have a clue as to what you want to achieve.

Re: Reg:Data Loading Issue

Tom — Fri, 02 Dec 2011 16:37:09 GMT

Here is a program that works for the three observations that you posted in the attachment above. You might need to adjust the record and variable lengths for files with longer records.

%let infile='c:\downloads\Raw_Data.txt';
%let nvars=21;
%let dlm=^;
filename tmpfile2 temp;
data _null_;
  infile &infile lrecl=1000 end=eof length=inchar ;
  file tmpfile2 lrecl=2000 ;
  length outline $2000 inline $1000 ;
  length outchar 8;
  length nbar1 nbar2 8;
  retain outline;
  retain outchar 0  ;
  do i=1 by 1 until( eof or (nbar1+nbar2 >= &nvars)) ;
    input inline $varying1000. inchar;
    nbar1 = lengthn(compress(outline,"&dlm",'K'));
    nbar2 = lengthn(compress(inline,"&dlm",'K'));
    if nbar1 + nbar2 < &nvars then do;
       substr(outline,outchar+1)=inline;
       outchar+inchar;
    end;
  end;
  putlog 'Line ' _n_ 'has ' outchar 'characters.';
  put outline $varying2000. outchar;
  outline=inline; outchar=inchar; 
run;

You will need to write a program that knows something about the actual variables to read the transformed text file.

Here is a program that just reads in the 21 variables as character strings and dumps them to the log.

data check ;
  infile tmpfile2 dlm="&dlm" dsd truncover ;
  length col1-col&nvars $200.;
  input col1-col&nvars;
  put (_N_ _all_) (=/);
run;

Here are the results.

_N_=1
col1=Is the time taken
col2=9865
col3=
col4=
col5=
col6=COOL
col7=dealt was good it is ok for us
col8=56
col9=ok
col10=AEFG
col11=
col12=13-aug-1999
col13=ok for it
col14=this is not prosable
col15=Ecoreco provides the fulsize reduction
col16=Registration link opens on  at 3.00 pm. Any registration attempts before the specified
col17=
col18=
col19=
col20=
col21=
_N_=2
col1=REQ1
col2=REQ2
col3=REQ3
col4=REQ4
col5=REQ5
col6=REQ6
col7=13-JUNE-1980
col8=REQ8
col9=REQ9
col10=REQ10
col11=REQ11
col12=13-MAR-1997
col13=REQ13
col14=REQ14
col15=REQ15
col16=WE AREOK
col17=REQ17
col18=REQ18
col19=REQ19
col20=12-FEB-2011
col21=UP
_N_=3
col1=REQ1
col2=REQ2
col3=REQ3
col4=REQ4
col5=REQ5
col6=REQ6
col7=13-JUNE-1980
col8=REQ8
col9=REQ9
col10=REQ10
col11=REQ11
col12=13-MAR-1997
col13=REQ13
col14=REQ14
col15=REQ15
col16=WE AREOK
col17=REQ17
col18=REQ18
col19=REQ19
col20=REQ20
col21=12-FEB-2011
NOTE: 3 records were read from the infile TMPFILE2.
      The minimum record length was 140.
      The maximum record length was 256.
      One or more lines were truncated.
NOTE: The data set WORK.CHECK has 3 observations and 21 variables.

Re: Reg:Data Loading Issue

Ksharp — Sat, 03 Dec 2011 10:20:13 GMT

If your output is like Tom's.

data want(keep=temp);
 infile 'c:\Raw_Data.txt' eof=last;
 length temp _temp $ 2000;
 retain temp _temp;
 input;
 temp=cats(temp,_infile_);
 if countc(temp,'^')ge 21 then do;temp=_temp;output;temp=_infile_;end;
 _temp=temp;
 return;
 last: output;
run;

Ksharp

Re: Reg:Data Loading Issue

Ksharp — Mon, 05 Dec 2011 02:01:08 GMT

data want(keep=temp);
 infile 'c:\Raw_Data.txt' eof=last;
 length temp _temp $ 2000;
 retain temp _temp;
 input;
 temp=cats(temp,_infile_);
 if countc(temp,'^')ge 21 then do;temp=_temp;output;temp=_infile_;end;
 _temp=temp;
 return;
 last: output;
run;
data want(keep=var:);
 set want;
 array _v{21} $ 100 var1-var21;
 do i=1 to 21;
  _v{i}=scan(temp,i,'^','m');
 end;
run;

Ksharp

Re: Reg:Data Loading Issue

R_Win — Tue, 06 Dec 2011 06:04:20 GMT

Re: Reg:Data Loading Issue

R_Win — Tue, 06 Dec 2011 06:22:20 GMT

Hi Tom actually my data structure was having huge length if i tryed on my own data it was not working i have attached my data structre can u help me..

Re: Reg:Data Loading Issue

Ksharp — Tue, 06 Dec 2011 06:31:57 GMT

Oh.God. Your data is very messy. But you did post what output you need, it is very hard to judge something belonged to which observation.

data want(keep=temp );
infile datalines ;
length temp $ 2000;
input a $char1. @@;
retain temp ; 
temp=strip(temp)||a;
pid=prxparse('/\d{2,}-\w{3,}-\d{2,}/o');
if prxmatch(pid,scan(temp,-1,'|')) then do;
output;call missing(temp);end;
datalines;
kumar  sd|raj  kl|67|23-a ug-90 muni  t|kunmar  h|90|12-j an-98 mani  y|raju  o|78 |12-feb- 78 raju  t|k  y|34|10-feb-92 output:output Name      surname  age  date kumar sd  raj k  67  23-aug-90 muni t    kunmar h 90  12-jan-98 mani y  raju o  78  12-feb-78 raju t  k y        34  10-feb-92 
;
run;

Ksharp

Re: Reg:Data Loading Issue

R_Win — Tue, 06 Dec 2011 07:00:16 GMT

Sharp sorry i have send the data my txt file actually if i used the code it was working but removing the spaces between the data can u see the file thqs for your patience

Re: Reg:Data Loading Issue

Ksharp — Tue, 06 Dec 2011 07:39:27 GMT

OK. you want to keep these blank too?

data want(keep=temp );
infile 'c:\x.txt' ;
length temp $ 400 b $ 1;
input a $char1. @@;
retain temp b;
if a='|' then count+1;
if missing(b) and count le 2 then temp=catx(' ',temp,a);
 else temp=cats(temp,a); 
pid=prxparse('/\d{2,}-\w{3,}-\d{2,}/o');
if prxmatch(pid,scan(temp,-1,'|')) then do;
                         output; count=0;
                         call missing(temp);
                           end;
b=a;
run;













data want(keep=temp );
infile datalines ;
length temp $ 400 b $ 1;
input a $char1. @@;
retain temp b;
if a='|' then count+1;
if missing(b) and count le 2 then temp=catx(' ',temp,a);
 else temp=cats(temp,a); 
pid=prxparse('/\d{2,}-\w{3,}-\d{2,}/o');
if prxmatch(pid,scan(temp,-1,'|')) then do;
                         output; count=0;
                         call missing(temp);
                           end;
b=a;
datalines;
kumar raj|sys engineer |45 |24-jan-
89

nani kumar|main frames user inn|56|1
2-feb-90

raju   jr|di user|
89|23-mar-90

;
run;

Ksharp

Re: Reg:Data Loading Issue

R_Win — Tue, 06 Dec 2011 12:29:56 GMT

data want(keep=temp); infile 'c:\Raw_Data.txt' eof=last; length temp _temp $ 2000; retain temp _temp; input; temp=cats(temp,_infile_); if countc(temp,'^')ge 21 then do; temp=_temp; output; temp=_infile_; end; _temp=temp; return; last: output; Hi ksharp i have used the above code when i run to this the length of temp and _temp was only 256 it was showing ,i have checked by length function so the data is missing after that although it was 2000 for both temp and _temp why ?