topic Re: find carriage return in dataset in SAS Programming

find carriage return in dataset

Maya_QC — Thu, 28 Aug 2014 02:32:48 GMT

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:

group	textline	flag
1	label variable status
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	1
2	label variable salary
2	1 from 10k to 20k
2	2 from 20K to 30K
2	3 from 30K
2	to 40K	1
2	4 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.

Re: find carriage return in dataset

RaviKommuri — Thu, 28 Aug 2014 07:14:47 GMT

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;

Re: find carriage return in dataset

RW9 — Thu, 28 Aug 2014 08:35:47 GMT

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.

Re: find carriage return in dataset

jakarman — Thu, 28 Aug 2014 08:45:17 GMT

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.

Re: find carriage return in dataset

RW9 — Thu, 28 Aug 2014 14:46:42 GMT

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;

put a $char1.;

EXIT:

run;

Re: find carriage return in dataset

jakarman — Thu, 28 Aug 2014 14:50:43 GMT

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