Define length in proc sql / proc append

[Belle]

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');

[Kurt_Bremser]

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

[ddemilla]

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!

[Belle]

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');

[LinusH]

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.

[Belle]

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

[Tom]

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;

[Belle]

Hi Tom,

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

Thank you for your suggestion.

[Patrick · Accepted solution]

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  

[Tom]

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 ...

[Patrick]

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.

[Tom]

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.

[Patrick]

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

[Ksharp]

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;

[Belle]

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