BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Belle
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

14 REPLIES 14
ddemilla
Fluorite | Level 6

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
Obsidian | Level 7

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
Tourmaline | Level 20

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
Belle
Obsidian | Level 7

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
Super User Tom
Super User

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
Obsidian | Level 7

Hi Tom,

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

Thank you for your suggestion.

Patrick
Opal | Level 21

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
Super User Tom
Super User

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
Opal | Level 21

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
Super User Tom
Super User

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
Opal | Level 21

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

Ksharp
Super User

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
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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