find carriage return in dataset

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

find carriage return in dataset

Hi everybody ,

I have problem to find carriage return in dataset. I imported the data from a text file, and my dataset looks like table below:

grouptextlineflag
1label variable status
11        from 10@20
12        from 20@30
13        from 30@40
14        from 40@50
15         from 50
1@601
2label variable salary
21      from 10k to 20k
22      from 20K to 30K
23       from 30K
2to 40K1
24        from  40K to 50K

So I created two variables group and flag. the variable group is to indicate values of each variable and  flag to point out the problem.  Now I need to extract just observations with problem and the label define name like my table below:

label variable status
5       from 50
@60
label variable salary
3        from 30K
to 40K


I tried many things , loops, SQl query , nothing works for me , so waiting for your help!

Thanks in advance for your help.


Accepted Solutions
Solution
‎08-28-2014 10:46 AM
Esteemed Advisor
Esteemed Advisor
Posts: 7,217

Re: find carriage return in dataset

Just to update you, I have had some code from Support which seems to work for me.  Try this (replace for your files):

data _null_;

     infile "s:\temp\rob\x.csv" recfm=n;

     file "s:\temp\rob\y.csv" recfm=n;

     retain Flag 0;

     input a $char1.;

     if a = '"' then

         if Flag = 0 then

             Flag = 1;

         else

             Flag = 0;

     if Flag = 1 then

         do;

         if a = '0D'x then

             do;

             goto exit;

             end;

         if a = '0A'x then

             do;

             goto exit;

             end;

         end;

     put a $char1.;

EXIT:

run;

View solution in original post


All Replies
Contributor
Posts: 45

Re: find carriage return in dataset

You can try use this code......

data have;                                      

infile datalines missover;                      

input @1 group @3 label $ @12 var_status $ 12-26;

datalines;                                      

1 1        from 10@20                           

1 2        from 20@30                           

1 3        from 30@40                           

1 4        from 40@50                           

1 5         from 50                             

1 @60                                           

2 1        from 10k to 20k                      

2 2        from 20k to 30k                      

2 3        from 30k                             

2 to 40k                                        

2 4        from 40k to 50k                      

;                                               

                                                

data want(drop= fromvar atvar tovar);           

set have;                                      

fromvar = find(var_status,'from');             

atvar = find(var_status,'@');                  

tovar = find(var_status,'to');                 

                                                

if group = 1 then                              

    if fromvar = 0 or atvar = 0;                

if group = 2 then                              

    if fromvar = 0 or tovar = 0;                

                                                

proc print data=want;run;                       

Esteemed Advisor
Esteemed Advisor
Posts: 7,217

Re: find carriage return in dataset

Hi,

I have pretty much exactly the same issue: https://communities.sas.com/thread/60374

Currently in my situation I have a csv file created by proc http which not only has carriage returns at the end of lines, but also embedded within the data.  It is a complete nightmare, have tried many different methods to no avail.  In my case I am taking this back to the data provider to get a resolution as there is no conceivable reason I can see that carriage returns should apper in text data.

As for a solution, well you could post process the imported data, however you will find that to be a real task as well, where does the data split, what has been read in and what needs to go where, I wouldn't recommend that at all, and why would you want to post process raw data in such a way when you are not the data provider.

Solution
‎08-28-2014 10:46 AM
Esteemed Advisor
Esteemed Advisor
Posts: 7,217

Re: find carriage return in dataset

Just to update you, I have had some code from Support which seems to work for me.  Try this (replace for your files):

data _null_;

     infile "s:\temp\rob\x.csv" recfm=n;

     file "s:\temp\rob\y.csv" recfm=n;

     retain Flag 0;

     input a $char1.;

     if a = '"' then

         if Flag = 0 then

             Flag = 1;

         else

             Flag = 0;

     if Flag = 1 then

         do;

         if a = '0D'x then

             do;

             goto exit;

             end;

         if a = '0A'x then

             do;

             goto exit;

             end;

         end;

     put a $char1.;

EXIT:

run;

Valued Guide
Posts: 3,206

Re: find carriage return in dataset

The carriage return (0Dx)  and lf (0Ax) are special characters that are used as record-indicators. They are processed at that level and not as all other bytes.

Using an unicode dataset is having even more impact on the meaning of bytes and characters.

Understanding the meaning is important.  For example some person was thinking a comma delimited file was handy. Than digits were passing by with having a comma.  uhh yes wat is the meaning of a comma?

 
Of course you can try many things , loops, SQl query and nothing will works.

Do you a binary input example of what you are trying to solve? In the worst scenario the file can be read binary (no reords) needing to that in a own way.

---->-- ja karman --<-----
Valued Guide
Posts: 3,206

Re: find carriage return in dataset

yes rw9 that is binary file processing (recfm=n). See the test on 0D and 0A in your code.

---->-- ja karman --<-----
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 562 views
  • 4 likes
  • 4 in conversation