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');
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
The length of the variables is too short in your base dataset (All). Correct that first with a data step before appending.
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!
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');
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.
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
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;
Hi Tom,
As I mentioned early, your example doesn't work for sql pass through.
Thank you for your suggestion.
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
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 ...
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.
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.
Yes, you are right. Missed the macro calls. So yes, either a cast() on Oracle level or a length/format on SAS level.
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;
Thank you All for all the useful information, I wish I could mark more 'useful' here.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: