topic Re: Define length in proc sql / proc append in SAS Procedures

Define length in proc sql / proc append

Belle — Mon, 13 Jul 2015 17:55:04 GMT

Hello,

I am trying to using proc sql to create several tables and then append them to be one table. The problem is when I append them, some of the contexts are truncated. I know we can define the length in data step, but I am wonder if there is a way that we can define the length in proc sql or proc append?

Thanks

Expecting results:

Type Category

AAAAA CCCCC

BBBBBBB CCCCCC

However, the program below gives the following result:

Type Category

AAAAA CCCCC

BBBBB CCCCC

%macro get_data(file=, type=, category=);

proc sql inobs=20;

connect to oracle as RemOracle (path=xxxx user=xxxx pw=xxxx);

create table &file as

select *

from connection to RemOracle

(select &type as Type

,&category as Category

from table

);

proc append base=All data=&file force; run;

quit;

%mend;

%get_data(file=file1, type='AAAAA', category='CCCCC');

%get_data(file=file2, type='BBBBBBB', category='CCCCCC');

Re: Define length in proc sql / proc append

Kurt_Bremser — Mon, 13 Jul 2015 18:17:34 GMT

The length of the variables is too short in your base dataset (All). Correct that first with a data step before appending.

Re: Define length in proc sql / proc append

ddemilla — Mon, 13 Jul 2015 18:18:56 GMT

You can actually use a format command right after your "as" variable declaration in proc sql. So you can simply add "format $32." after "Type" in your subqueary.

Hope that helps!

Re: Define length in proc sql / proc append

Belle — Mon, 13 Jul 2015 19:33:45 GMT

I think the problem is I'm using sql pass through, but I was able to add length in it, it works.

Is there a way to simply my codes below to avoid repeated steps?

Thanks

%macro get_data(file=, type=, category=);

proc sql inobs=20;

connect to oracle as RemOracle (path=xxxx user=xxxx pw=xxxx);

create table &file as

select &type length=30 as Type

,&category length =30 as Category

,Total

from connection to RemOracle

(select &type as Type

,&category as Category

,count(*) as Total

from table

group by &type

,category

);

proc append base=All data=&file force; run;

quit;

%mend;

%get_data(file=file1, type='AAAAA', category='CCCCC');

%get_data(file=file2, type='BBBBBBB', category='CCCCCC');

Re: Define length in proc sql / proc append

LinusH — Mon, 13 Jul 2015 20:20:50 GMT

I see no reason to use explicit SQL pass thru in this particular case. Implicit pass thru leads to easier coding. Using SQL insert let you omit storing the intermediate result in a work table. The downside is that it's not as tolerant as proc append.

Re: Define length in proc sql / proc append

Tom — Mon, 13 Jul 2015 20:31:18 GMT

FORMAT will just set the format attached to the variable. Use LENGTH to set the length of the variable. Try this program.

proc sql ;

create table class as

select name format=$20.

from sashelp.class

;

quit;

proc contents data=class;

run;

Re: Define length in proc sql / proc append

Belle — Mon, 13 Jul 2015 22:01:43 GMT

Hi Linus,

I have to since I'm pulling from huge datasets. I didn't show the full program. I just use this as an example.

Thanks

Re: Define length in proc sql / proc append

Belle — Mon, 13 Jul 2015 22:02:37 GMT

Hi Tom,

As I mentioned early, your example doesn't work for sql pass through.

Thank you for your suggestion.

Re: Define length in proc sql / proc append

Patrick — Mon, 13 Jul 2015 22:30:29 GMT

If you want to define the data type and length in the pass-through block then you need to do it in the syntax of the data base you're accessing. For Oracle this would be something like:

from connection to RemOracle

(select &type as Type

,cast(&category as varchar2(32 byte)) as Category

,count(*) as Total

from table

group by &type

,category

);

Should you have a general problems with "not matching" lengths if pulling data from the data base then have a look into option ADJUST_BYTE_SEMANTIC_COLUMN_LENGTHS and related options SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition

Re: Define length in proc sql / proc append

Tom — Tue, 14 Jul 2015 11:08:31 GMT

Works fine for variables coming from a pass-through query. But you need to list the variables on the SAS side, and not just use SELECT * FROM CONNECTION ...

Re: Define length in proc sql / proc append

Patrick — Tue, 14 Jul 2015 11:20:04 GMT

This doesn't really explain why there would be string truncation as the variable length in SAS should be determined by the type and length of the source variables in Oracle. The code the OP posted doesn't show us to what the macro variables resolve but if it's only to variable names then the only explanation for string truncation I can think of is a miss-match with the "byte semantics" stuff.

Re: Define length in proc sql / proc append

Tom — Tue, 14 Jul 2015 11:53:28 GMT

That might be possible, but is not the problem that was originally posted. The original problem was getting length mismatches when combined the results of different queries.

Re: Define length in proc sql / proc append

Patrick — Tue, 14 Jul 2015 12:00:05 GMT

Yes, you are right. Missed the macro calls. So yes, either a cast() on Oracle level or a length/format on SAS level.

Re: Define length in proc sql / proc append

Ksharp — Tue, 14 Jul 2015 12:08:05 GMT

Use UNION CORR operator .SQL will take the maximum length of these variables.

proc sql;

create table want as

select * from A

UNION CORR ALL

select * from B;

quit;

Re: Define length in proc sql / proc append

Belle — Tue, 14 Jul 2015 21:05:53 GMT

Thank you All for all the useful information, I wish I could mark more 'useful' here.