Help using Base SAS procedures

Define length in proc sql / proc append

Accepted Solution Solved
Reply
Contributor
Posts: 67
Accepted Solution

Define length in proc sql / proc append

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


Accepted Solutions
Solution
‎07-13-2015 06:30 PM
Respected Advisor
Posts: 4,173

Re: Define length in proc sql / proc append

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  

View solution in original post


All Replies
Super User
Posts: 7,758

Re: Define length in proc sql / proc append

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 34

Re: Define length in proc sql / proc append

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!

Contributor
Posts: 67

Re: Define length in proc sql / proc append

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

Super User
Posts: 5,424

Re: Define length in proc sql / proc append

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.

Data never sleeps
Contributor
Posts: 67

Re: Define length in proc sql / proc append

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

Super User
Super User
Posts: 7,037

Re: Define length in proc sql / proc append

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;

Contributor
Posts: 67

Re: Define length in proc sql / proc append

Hi Tom,

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

Thank you for your suggestion.

Solution
‎07-13-2015 06:30 PM
Respected Advisor
Posts: 4,173

Re: Define length in proc sql / proc append

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  

Super User
Super User
Posts: 7,037

Re: Define length in proc sql / proc append

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

Respected Advisor
Posts: 4,173

Re: Define length in proc sql / proc append

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.

Super User
Super User
Posts: 7,037

Re: Define length in proc sql / proc append

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.

Respected Advisor
Posts: 4,173

Re: Define length in proc sql / proc append

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

Super User
Posts: 10,018

Re: Define length in proc sql / proc append

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;

Contributor
Posts: 67

Re: Define length in proc sql / proc append

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 14 replies
  • 2052 views
  • 6 likes
  • 7 in conversation