topic Re: Column with varying data lengths in SAS Enterprise Guide

Column with varying data lengths

jen123 — Mon, 21 Jan 2013 22:55:12 GMT

I have a .xlsx file that I have to convert to .csv to use in SAS EG. It is my understanding that SASEG 4.3 does not allow import of .xlsx files.

There is one column in the file with data of varying lengths. Here is a sample of the column (with is alpha-numeric). As you can see, there are 7-digit, 8-digit and 9-digit numbers. The cells with 4-digits are missing the leading 6 zeros.

GOC_Number

2051

2098

1554630

1994104

2510107

10351204

10351235

100026820

100026823

I tried Text-to-Column in the .xlsx file then save it as .csv. That did not work - it did not save the column as text.

I also tried this code change the variable length to z10. That added zeros to the cells that are 7,8 or 9 digits lone.

Any thoughts/advice?

Re: Column with varying data lengths

AncaTilea — Mon, 21 Jan 2013 23:12:08 GMT

Hi.

I am not familair with EG, but if possible, you could calculate the length of your variable:

var_length = length(your_var);

if var_length = 4 then do;

format your_var z10.;

end;

So this snippet should only add the leading zeroes to the entries that are 4 digits.

Good luck!

Anca.

Re: Column with varying data lengths

twocanbazza — Mon, 21 Jan 2013 23:31:44 GMT

Why you trying to determine the length, wouldn't yuo just read the csv in delimited by ',' and define the variable in SAS as numeric 8.? and then apply the z10. format or put as char using z10.

Also if you just save teh file as CSV it should work, or try saving as 2003-2007 xls.

Re: Column with varying data lengths

jen123 — Mon, 21 Jan 2013 23:44:37 GMT

I would prefer to work in 2003-2007 .xls, but the file has more than 65K rows.

Re: Column with varying data lengths

jen123 — Tue, 22 Jan 2013 03:15:21 GMT

I tried this code but got an error. Any advice please.

data test;

set jen1.emp_current

primary_goc=length(primary_goc);

if primary_goc=4 then do;

format primary_goc z10.;

run;

15 data test;

16 set jen1.emp_current

17 primary_goc=length('primary_goc');

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, -, :, ;, END, INDSNAME, KEY, KEYS, NOBS,

OPEN, POINT, _DATA_, _LAST_, _NULL_.

ERROR 76-322: Syntax error, statement will be ignored.

Re: Column with varying data lengths

SASKiwi — Tue, 22 Jan 2013 03:29:35 GMT

Missing semicolon in line 16:

data test;

set jen1.emp_current;

primary_goc=length('primary_goc');

Re: Column with varying data lengths

AncaTilea — Tue, 22 Jan 2013 13:01:51 GMT

Hi Jen,

In this line of code:

primary_goc = length('primary_goc');

you are replacing the value of primary_goc with its length.

I think you should define a new variable

l_primary_goc = length(primary_goc); if l_primary_goc = 4 then....

Just a thought.

Regards,

Anca.

Re: Column with varying data lengths

jen123 — Tue, 22 Jan 2013 13:31:45 GMT

I fixed the code and added the semi-colon.

data test;

set jen1.emp_current;

primary_goc=length(primary_goc);

if primary_goc=4 then do;

format primary_goc z10.;

run;

I got the following error:

24 if primary_goc=4 then do;

25 format primary_goc z10.;

____

484

NOTE 484-185: Format $Z was not found or could not be loaded.

26 run;

117

ERROR 117-185: There was 1 unclosed DO block.

I changed z10. to $10. and that seemed to fix the issue. However, I still got the same error on line 26.

Re: Column with varying data lengths

AncaTilea — Tue, 22 Jan 2013 13:34:37 GMT

After your format statement you need an end;

data test;

set jen1.emp_current;

primary_goc=length(primary_goc);

if primary_goc=4 then do;

format primary_goc z10.;

end;

run;

Thanks.

Anca.

Re: Column with varying data lengths

jen123 — Tue, 22 Jan 2013 13:55:41 GMT

Hi Anca,

I ran this code:

data test;

set jen1.emp_current;

a_primary_goc=length(primary_goc);

if a_primary_goc=4 then do;

format primary_goc $10.;

end;

run;

Thoughts?

The results:

a_primary_goc column is = to 4 for all rows

primary_goc still in 4-digits.

Re: Column with varying data lengths

AncaTilea — Tue, 22 Jan 2013 14:06:57 GMT

data test;

input goc_number $;

cards;

2051

2098

1554630

1994104

2510107

10351204

10351235

100026820

100026823

;

data temp;

set test;

length_goc=length(goc_number);

if length_goc=4 then do;

temp_goc = compress('000000' || goc_number);

format temp_goc $10.;

end;

if temp_goc ne "" then final_goc = temp_goc;

else final_goc = goc_number;

format final_goc $10.;

run;

Is this what you'd like?

Re: Column with varying data lengths

Tom — Tue, 22 Jan 2013 15:09:17 GMT

Looking at the various error messages you have posted your variable PRIMARY_GOC is a character variable and not a numeric variable.

Also you seem to be trying to treat the FORMAT statement as if it was an executable statement. The FORMAT statements are compiled so putting them inside of an IF/THEN conditional will have no effect.

So do you want the character strings in PRIMARY_GOC that look like they might be numbers to be left padded with zeros?

primary_goc = put( input(primary_goc,10.),z10.);

Re: Column with varying data lengths

jen123 — Tue, 22 Jan 2013 15:44:46 GMT

Tom - You are correct. It's a character variable since it's alpha-numeric.

As I stated in the original post, the issue is that in this column there are cells of different lengths. Some are 7, 8, 9 and 10 digits long. The ones that are 4-digits long should be 10 digits.

If I used as you suggested, it would zeros in front of the 7,8 and 9 digit cells, which I do not want.

That data is what it is...

Re: Column with varying data lengths

Tom — Tue, 22 Jan 2013 15:49:29 GMT

If the issue is that you want all values that are four characters long to be prefixed with six zeros then that answer was posted above.

if length(primary_goc) = 4 then primary_goc='000000'||primary_goc;